CODEWITHSUNDEEP
vbaexcel
miken83
miken83

Reputation: 13

VBA Macro to delete rows based on the users input

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.

Thanks!

Upvotes: 0

Views: 5351

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

Upvotes: 3

er_jack
er_jack

Reputation: 112

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

Upvotes: 0

jrad
jrad

Reputation: 3190

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

Upvotes: -1

Related Questions