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

copy data from sheet to another with ignore blank cells

0

Hi guys

 I  want  copying  the  whole  data  from  sheet1  to  sheet2   but    I    don't  copy  any  row  contains  the  whole  blank  range  from C2: H 600 H  . if  the  columns  C:H  into sheet1 (the  whole  cells  in columns C,D,E,F,G,H should  delete  entire  row  if  they  are blank cells together   )   . when  contains  blank  cells  then  shouldn't  copy  rows  should  ignore  it   when  copy  to  sheet2  with  considering  when  run  the  macro  more  than  one  time  should not  copy  data  to  the  bottom  repeatedly .

briefly  the  rows where  contains  whole columns  C,D,E,F,G,H  blank cells  should  not  copy  to  sheet2  at all 

I put  a little  result  in sheet2  

this  code  doesn't  work  for  me  correctly 

Sub testest()
    Dim lrow, lrow1 As Long
    Dim rng As Range
    lrow = Sheet1.Range("T" & Rows.Count).End(xlUp).Row
    lrow1 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    Sheet1.Range("T3:AA" & lrow).SpecialCells(xlCellTypeVisible).Copy
    Sheet2.Range("A1").PasteSpecial
    For Each rng In Sheet2.Range("c2:h" & lrow1)
    If Application.WorksheetFunction.CountBlank(rng) = rng.Cells.Count Then rng.EntireRow.Delete
Next rng
End Sub
Answer
Discuss

Answers

0
Selected Answer

Hi Leopard

In the attached revised file,  I've just used a simple loop to go BACKWARDS up the copied data, deleting any rows with blanks in C:H. I've made some changes to your code (in bold) and added explanatory comments (as below).

Sub testest()
    Dim lrow, lrow1 As Long
    Dim rng As Range, n As Long

    lrow = Sheet1.Range("T" & Rows.Count).End(xlUp).Row
    'clear sheet2
    Sheet2.UsedRange.EntireRow.Delete
    'copy/ paste data
    Sheet1.Range("T3:AA" & lrow).SpecialCells(xlCellTypeVisible).Copy
    Sheet2.Range("A1").PasteSpecial
    'remove copy dotted lines
    Application.CutCopyMode = False
    'get last row of copied data
    lrow1 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    'speed up for next part
    Application.ScreenUpdating = False
    ' loop backwards through sheet2
    For n = lrow1 To 2 Step -1
        Set rng = Sheet2.Range("c" & n & ":h" & n)
        If Application.WorksheetFunction.CountBlank(rng) = rng.Cells.Count Then rng.EntireRow.Delete
    Next n
    Application.ScreenUpdating = True
    Sheet2.Range("A1").Select
End Sub

Note however that using the worksheet function seems to slow the code down a lot (even with screen updating disabled) so you'll need to wait a bit until the results appear on sheet2 (which leaves me empty).

Hope this makes sense and helps.

Discuss

Discussion

Hi John,
Just  qustion   why  when  loop     the  code  takes  more  time  when  run(seem  too slow)  despite  of   data are  not  big  just  about  649  rows?
I'm  surprised  from  this  case .
thanks  for  your  solution !
leopard (rep: 72) Jun 17, '22 at 6:04 am
Thanks for selecting my answer Leopard. Not sure why it runs so slowly with just a few hundered lines. Suspect it is caused by the repeated use of the Worksheet Function (which could be avoided by using arrays) . 
John_Ru (rep: 3992) Jun 17, '22 at 8:20 am
Add to Discussion


Answer the Question

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