VBA Rows Count Not Resetting

[BrainDiseasee]

Hello,

I have written a code to copy a set range from every ws except the ones i exclude. And to start from the bottom and go up to the last cell row with data then offset by one paste values in that destination. Not sure my problem is in my code as i just tried on a blank sheet and it worked as expected. But the problem is after the first set of data pastes, instead of going to the next available row it jumps down by a few hundred rows.

Option Explicit
Sub CopyIt()

Sheets("Project_Hours").Select
Range("A2:E7720").Cells.Clear

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Master_Data" And ws.Name <> "Resource_Hours" And ws.Name <> "Chart_01" And ws.Name <> "Project_Hours" And ws.Name <> "Template" And ws.Name <> "PRJ_Table" And ws.Name <> "Team_Dashboard" And ws.Name <> "Archive_Data" Then
ws.Range("B39:F518").Copy
Sheets("Project_Hours").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues

End If
Next
EndSub

 

[igold]

Your code as written works for me with my small sample fictitious data. Is it possible that in one of these ranges

ws.Range("B39:F518").Copy

there are formulas that evaluate to "". Excel will still copy those over and even though they appear to be blank, Excel will evaluate the last row with data at the end of these "blank cells".

 

[BrainDiseasee]

Hello,

Thanks for your reply. I actually figured it out kind of. Part of my range was blank cells but for some reason excel wouldnt recognize them as blanks so when i sued Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete it wouldnt work i just changed the formulas in the cells to instead of = "" to equal 0 then it removed everything properly.