Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

skip blank cells when copy data from specific range

0

Hi

I  search  for  way  to  skip  blank  cells  for  range  in this  line

         ar = .Sheets(5).Range("g2:i1000").Value


somtimes  I  have  few  data in  range G2:I100    the  others  cells  will  be  blank . so  I  search  for  way  to  skip  blank  cells  by  just  import  data   are  filled  in cells  with ignore  empty  cells . I  try  two  ways:

1- add condition 

If ar <> "" Then

2- change  range  to 

ar=sheets(5).Range("G2:I" & Cells(Rows.Count, "G").End(xlUp).Row)

two both ways  don't  work and  shows errors.

 


 Sub ge_tdata()
 Dim ar As Variant, lRw As Long


 ' get last row number in column B
 lRw = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row


 With Application.FileDialog(msoFileDialogFilePicker)
   If .Show Then
      With GetObject(.SelectedItems(1))
         ar = .Sheets(5).Range("g2:i1000").Value
         If ar <> "" Then
         ' copy to bottom of row B
         ThisWorkbook.Sheets("sheet1").Cells(lRw + 1, 2).Resize(UBound(ar), UBound(ar, 2)) = ar
        .Close 0
        End If
      End With
   End If
 End With
End Sub

I  hope  to find  idea  to  fix  this problem .

Answer
Discuss

Answers

0
Selected Answer

Hi Malkal

I suggest you filter the data in the other file then copy/ paste special into your workbook.

The code below doesn't use the Variant ar but instead defines a range and writes a "helper" column giving the value TRUE if there's anything in G,H or I or a given row. It then autofilters on that.

I've renamed your macro, put some key bits in bold and added comments so you can see what is happening:

Sub get_data()
 Dim lRw As Long
 Dim OUTrng As Range

 ' get last row number in column B
 lRw = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row

 With Application.FileDialog(msoFileDialogFilePicker)
   .Show
      With GetObject(.SelectedItems(1))
        Set OUTrng = .Sheets(5).Range("g2:i100")
        ' add helper column then filter on that
        With OUTrng
            Set OUTrng = OUTrng.Resize(.Rows.Count, .Columns.Count + 1)
        End With
        ' filter for G, H and I = blank or 0
        With OUTrng
            ' add formula to helper column then filter on that
            .Columns(.Columns.Count).Formula = "=If(OR(G" & .Row() & ">0, H" & .Row() & ">0, I" & .Row() & ">0),TRUE, FALSE)"
            .AutoFilter .Columns.Count, "TRUE"
            ' copy filtered data without helper column
            .Resize(.Rows.Count, .Columns.Count - 1).SpecialCells(xlCellTypeVisible).Copy
        End With
        ' close file without saving
        .Close 0
        ' paste to bottom of row B but don't use helper column
        ThisWorkbook.Sheets("sheet1").Cells(lRw + 1, 2).PasteSpecial
      End With
End With

End Sub

Just be careful- if the data can have a blank in G (but not H or I) and the macro adds a row where B is blank in your file, lRw will ignore that when the macro next runs and overwrite the data of that row.

Hope this fixes your problem.

Discuss

Discussion

Hi John,
Just be careful- if the data can have a blank in G (but not H or I) and the macro adds a row where B is blank in your file, lRw will ignore that when the macro next runs and overwrite the data of that row.
thanks  for  clarification  this  point 
again thanks  very  much  for  your  supporting   and  solution .
Malkal (rep: 22) Dec 3, '22 at 7:11 am
Thanks for selecting my Answer Malkal. (I just corrected the spelling of your name in my Answer) 
John_Ru (rep: 6102) Dec 3, '22 at 10:04 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login