• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to copy B cell if not null and A in front of it as well

ASABDELHALIM

New Member
Hi
Need your help for this code :

>>> use code - tags <<<
Code:
Sub Copy()

Dim i As Integer
'Dim j As Integer
j = Worksheets("sheet1").Columns("B").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

For i = 2 To j

If Cells(i, 2) = "" Or Cells(i, 2) = 0 Then

Sheet1.Range("A1").End(xlDown).Offset(1, 0).Value = Sheet2.Range("A" & i).Value
Sheet1.Range("B1").End(xlDown).Offset(1, 0).Value = Sheet2.Range("B" & i).Value

End If

Next i

End Sub
Attched file have 2 coulmns so if B cell in sheet1 is not null then copy B cell and A cell in fornt of it in sheet2
Kindly your help

Thank You
 

Attachments

Last edited by a moderator:

ASABDELHALIM

New Member
Hi
Thanks so much, but need your help in below case .
1- keep headers in sheet2 as A and B only as they are " Fixed"
2- In case B cell is not null then B3 is not null. Hence, copy A3 and B3 into A2 and B2 in sheet2
2- Next, copy A5 and B5 into A3 and B3 in sheet2 ... and so on

Thank you
 

Marc L

Excel Ninja
Hi !​
if B cell in sheet1 is not null then copy B cell and A cell in fornt of it in sheet2
First, according to forum rules do not forget to use the code tags …​
As it can be easily achieved without any code just using Excel basics like a filter - so at kid level ! - then just activating​
the Macro Recorder and operating manually you can get your own code base with just the necessary​
like for example without the useless SpecialCells method !​
Then for a clean code remove any useless Activate and Select statements just playing directly with Excel objects …​
According to your initial attachment as a VBA beginner starter :​
Code:
Sub Demo1()
         Application.ScreenUpdating = False
         Sheet2.UsedRange.Offset(1).Clear
    With Sheet1.UsedRange.Columns
        .Item(2).AutoFilter 1, "<>"
        .Item("A:B").Offset(1).Copy Sheet2.[A2]
        .AutoFilter
    End With
         Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

ASABDELHALIM

New Member
Hi
Thank you one more and last case, i need to select only coulmn A once filter done then copy values in coulmn A starting A2 and then paste it in sheet2 starting A2 " copy and paste without headers"
Thank You
 

ASABDELHALIM

New Member
1- I have written Range("A2:A" & Lrow).SpecialCells(xlCellTypeVisible).Select " instead of" --> 'Selection.SpecialCells(xlCellTypeVisible).Select
2- change " Range("A1").Select" to become ---- > Range("A2").Select

Now it works fine
 

Attachments

Marc L

Excel Ninja
As my answers are obviously based on my demonstration … :rolleyes: So very not for those useless codelines ! Just well reading post #5 …​
 
Top