I am trying to find a macro that will delete all rows below a user specified number. For example the user will put in "19" and the macro will delete rows 20 and below. I really appreciate the help.
Try this. I have used
ActiveSheet. You can set it to any sheet that you would like to work with.
Sub Sample() Dim Ret As Long Ret = Application.InputBox(Prompt:="Please enter a row number", Type:=1) If Ret = False Then Exit Sub With ActiveSheet .Rows(Ret + 1 & ":" & .Rows.Count).Delete End With End Sub
You can enter into column=1 and Row=1 the number of the row you want to keep. The data is in rows=deleterowsbefore every row back to row=2 will be deleted.
Sub Macro1() ' ' Macro1 Macro ' deleterowsbefore = ActiveSheet.Cells(1, 1) Rows("2:" & deleterowsbefore - 1).Select Selection.Delete Shift:=xlUp End Sub
This should do the trick:
Sub UserInput() Dim num As Integer num = InputBox(Prompt:="Enter the row number.", Title:="Enter Row Number", Default:="Row number") If (num < 1) Then GoTo Whoops: End If Dim i As Integer i = 1 Do While i <= num Rows(1).EntireRow.Delete i = i + 1 Loop GoTo Fin: Whoops: MsgBox ("You have entered an invalid row number") Fin: End Sub