Macro to end of spreadsheet

[Dlanders05]

It can't be this hard. Please. ...and thank you so much.

• Data is all in column A.
• Using a Cut/Paste to place data in a single row.
• Need macro to go to end of spreadsheet.
• Delete all blank rows.
• Would like to run this macro in multiple spreadsheets.

Sub NIC()
'
' NIC Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Range("A431").Select
Selection.Cut Destination:=Range("C430")
Range("A432").Select
Selection.Cut Destination:=Range("B430")
Range("A433").Select
Selection.Cut Destination:=Range("D430")
Range("D430").Select
ActiveWindow.SmallScroll Down:=8
Range("A435").Select
Selection.Cut Destination:=Range("C434")
Range("A436").Select
Selection.Cut Destination:=Range("B434")
Range("A437").Select
Selection.Cut Destination:=Range("D434")
Range("D434").Select
End Sub

 

[Monty Norman]

It can't be this hard. Please. ...and thank you so much.

• Data is all in column A.
• Using a Cut/Paste to place data in a single row.
• Need macro to go to end of spreadsheet.
• Delete all blank rows.
• Would like to run this macro in multiple spreadsheets.

Sub NIC()
'
' NIC Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
Range("A431").Select
Selection.Cut Destination:=Range("C430")
Range("A432").Select
Selection.Cut Destination:=Range("B430")
Range("A433").Select
Selection.Cut Destination:=Range("D430")
Range("D430").Select
ActiveWindow.SmallScroll Down:=8
Range("A435").Select
Selection.Cut Destination:=Range("C434")
Range("A436").Select
Selection.Cut Destination:=Range("B434")
Range("A437").Select
Selection.Cut Destination:=Range("D434")
Range("D434").Select
End Sub

Hello
"It can't be this hard." If this is true why are you asking for help?
Try something like:

Sub GoToTheEnd()
Dim lLastRow As Long

lLastRow = ActiveSheet.Range("A1").End(xlDown).Row
End Sub

lLastRow will have the row number of the last occupied cell. From there you can navigate to where you need to go. Remember there are 1048576 rows in a worksheet (Excel 2007 and newer)

This will go to the last occupied cell if:
A) There are no empty (blank) cells in the column you are working with. In this case it will be Column A.
B) There is data past a certain number of rows, in this case Row 1.

Play with this, and if you still need help come back with your questions

Monty

 

[snjpverma]

Can you post a sample of how your data looks like? And also a sample of the output that you are looking for? Because the cell references in your VBA code looks random to me.

As for the last row in column A. You can use this:

Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row