VBA to exclude empty string cells

[Mr Krabs]

Hi,
I'm very new to VBA and need some help.
I want to copy the data in a specific range but exclude the rows with cells containing "".

So far I have this code but it selects all cells in the range because some cells contains a formula resulting in empty string "", meaning they are not blank according to Excel.

Sub CopyData()

    Sheets("Sheet2").Select
With Worksheets("Sheet2")
    .Range("B2", .Range("AD" & Rows.Count).End(xlUp)).Copy
    
End With
    Sheets("Sheet1").Select

End Sub

The macro will be assigned to a Form Control Button located on Sheet 1.

Any help greatly appreciated!

 

[DanteAmor]

Hi and welcome to MrExcel

You want to copy the cell range from B to AD.
For example, in row B2: D2, in cell E2 there is "", so it is not copied.
Or if all cells B2 through D2 are all equal to "", then that row is not copied.
Do you want to paste on sheet1, in which row?

 

[Mr Krabs]

Hi and welcome to MrExcel

You want to copy the cell range from B to AD.
For example, in row B2: D2, in cell E2 there is "", so it is not copied.
Or if all cells B2 through D2 are all equal to "", then that row is not copied.
Do you want to paste on sheet1, in which row?

Thanks!

I want to select and copy all cells from B2 to last row in AD containing other than "". AD2:AD12 contains formula giving either a result or "". If I clear AD12 my code copies B2:AD11 even though the other columns contain data on row 12, so the content in last cell in AD column seem to decide the selection. I don't want to clear the data though, the formula should be left untouched.

It will be pasted manually outside of Excel.

 

[DanteAmor]

Try this:

Sub Macro1()
  With Sheets("Sheet2")
    With .Range("B2", .Range("AD" & Rows.Count).End(3))
      .AutoFilter Field:=30, Criteria1:="<>"
      .Copy
      Sheets("Sheet1").Range("A1").PasteSpecial xlAll
      .AutoFilter
    End With
  End With
End Sub

 

[Mr Krabs]

Try this:

Sub Macro1()
  With Sheets("Sheet2")
    With .Range("B2", .Range("AD" & Rows.Count).End(3))
      .AutoFilter Field:=30, Criteria1:="<>"
      .Copy
      Sheets("Sheet1").Range("A1").PasteSpecial xlAll
      .AutoFilter
    End With
  End With
End Sub

I can't get this code to work unfortunately. I get "Run-time error '1004': AutoFilter method of Range class failed"

 

[DanteAmor]

You should have data on sheet2 from column A through AD.
You can put a sample of your data here. If it is confidential data, replace it with generic data.
Use XL2BB tool