I have four worksheets (see sample). The goal is to copy Col A from the first three worksheets to Col A of the fourth worksheet, sort the result and get rid of empty rows. Here is what I have done so far in the code below:
Delete old data from Col A of table on Worksheet 4
Copy Col A of Worksheet 1 to Col A of Worksheet 4
Copy Col A of Worksheet 2 to Col A of Worksheet 4
Copy Col A of Worksheet 3 to Col A of Worksheet 4
Sort Col A of Worksheet 4 to separate data from empty cells
Sub GetData()
Dim NumRows As Long
Dim NextRow As Long
Dim Rw As Long
Dim ws1 As Worksheet: Set ws1 = Sheets("One")
Dim ws2 As Worksheet: Set ws2 = Sheets("Two")
Dim ws3 As Worksheet: Set ws3 = Sheets("Three")
Dim ws4 As Worksheet: Set ws4 = Sheets("Four")
Dim rng1 As Range: Set rng1 = ws1.Range("A2", ws1.Cells(Rows.Count, 1).End(xlUp))
Dim rng2 As Range: Set rng2 = ws2.Range("A2", ws2.Cells(Rows.Count, 1).End(xlUp))
Dim rng3 As Range: Set rng3 = ws3.Range("A2", ws3.Cells(Rows.Count, 1).End(xlUp))
Dim rng4 As Range: Set rng4 = ws4.Range("A2", ws4.Cells(Rows.Count, 1).End(xlUp))
'Prepoare receiving worksheet by deleting prior content
ws4.Activate
NumRows = ActiveSheet.UsedRange.Rows.Count
Range("A2:A" & NumRows).ClearContents
Range("A3:G" & NumRows).Delete
'Copy Column1 of Worksheet One and paste to Column1 of Worksheet Four
rng1.Copy Destination:=ws4.Range("A2")
'Find out where the pasted data ends
With ws4
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
'Copy Column1 of Worksheet Two and paste to Column1 of Worksheet Four
rng2.Copy Destination:=ws4.Range("A" & NextRow)
'Find out where the pasted data ends
With ws4
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
'Copy Column1 of Worksheet Three and paste to Column1 of Worksheet Four
rng3.Copy Destination:=ws4.Range("A" & NextRow)
'Sort the worksheet to force empty rows to the bottom of the table
ActiveWorkbook.Worksheets("Four").ListObjects("tblFour").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Four").ListObjects("tblFour").Sort.SortFields.Add _
Key:=Range("tblFour[Column1]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Four").ListObjects("tblFour").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'--------------------------------------------------------
'This is where I am stuck.
'I need to delete the empty rows below the pasted data
'--------------------------------------------------------
End Sub
My original idea was to copy the data into an array or variable, sort the data to exclude the empty cells and then paste the data into Worksheet 4 but I am sorely lacking in the proper skills.
Would you kindly look this over and suggest a better way?
Thanks!
Bookmarks