Macro: Merge Sheets with variable row count

[shane_aldrich]

Hi all needing help with a macro...

I'm merging data from a sheet2 to the top of sheet1, so I need a macro that will count the number of non-blank rows in column e of sheet2...

Once the count is determined, starting at row 1, highlight that number of rows, and cut them and insert them above row1 on sheet1,

Any help would be much appreciated

 

[shane_aldrich]

Figured it out if anyone could use.

Sub d()
'Formulas Filldown A_AH
Dim Column_A_AH As Long
Column_A_AH = Range("E1:E" & Range("E1").End(xlDown).Row).Rows.Count
Range("A1" & ":AH" & Column_A_AH).Select
Selection.Cut
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
End Sub

 

[JPM]

Maybe not the best VBA code but it should work:

Sub Macro1()
Dim lLastRow As Long
Sheets("Sheet2").Select
On Error GoTo ErrorHandler
With ActiveSheet
lLastRow = .Cells.Find _
(What:="*", After:=.Range("A1"), SearchDirection:=xlPrevious).Row
End With
ActiveSheet.Select
Range("A" & lLastRow).Select

ErrorHandler:
If Err.Number <> 0 Then
Range("A3").Select
End If

Result = ActiveCell.Row
Rows("1:" & Result).Select
Selection.Cut
Sheets("Sheet1").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown

End Sub