I am new to this forum and am teaching myself VBA (I know I need to do a course)
If anyone can assist me on this mater I would be appreciative
I have a group of cells in 10 rows ("a1:j60) in my code below. .
I copy them to another worksheet
I then sort the destination worksheet to have the most recent entries at the top.
The problem is that when I do not have 10 rows of data I am getting the blank lines copied to my target spreadsheet.
The blank lines are not necessarily in order (i.e. rows 3, 5 and 8 might be blank on one update... then rows 2,& 3 on the next update).
Is there a simple solution to not copy the blank cells or do I have to run a macro 10 times with an if command for each row?
Any assistance will be greatly appreciated.
Here is my code:
'Step 1 - Update Data to TargetSheet
ActiveWorkbook.Worksheets("DataSheet").Range("a51:j60").Copy
With ActiveWorkbook.Worksheets("TargetSheet")
lNextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(lNextRw, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End With
'clear clipboard
Application.CutCopyMode = False
'Step 2 sort TargetSheet in descending order
Sheets("TargetSheet").Select
Cells.Select
ActiveWorkbook.Worksheets("Results Archive").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Results Archive").Sort.SortFields.Add Key:=Range( _
"A2:A5000"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("TargetSheet").Sort
.SetRange Range("A1:j5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Thanks in advance
Bookmarks