delete rows

[john_cash]

Hello everyone,
I ask for help to delete rows:

An alert "how many rows you want to delete?"
example 50
starting from row 5 it eliminates 50 rows

must re-enter the formulas for the automatic number in column A
I hope I explained myself
john

[PaulM100]

This one should do

Sub UserInput()
    Dim num As Integer
    Dim Lastrow As Long
    num = InputBox(Prompt:="Delete Rows?", Title:="Enter the number of Rows", Default:="How Many?")
    If (num < 1) Then
        GoTo Whoops:
    End If
    Dim i As Integer
    i = 1
    Do While i <= num
        Rows(6).EntireRow.Delete
        i = i + 1
    Loop
    GoTo Fin:
Whoops:
        MsgBox ("You have entered an invalid row number")
Fin:
Range("A6").Value = 1
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A7").FormulaR1C1 = "=IFERROR(IF(RC[1]<>"""",R[-1]C+1,""""),"""")": Range("A7:A" & Lastrow).FillDown
End Sub

[john_cash]

Hi poul, thanks you
An error if I click cancel or click x goes to debug.

[PaulM100]

This one should handle that:

Sub UserInput()
    Dim num As Integer
    Dim Lastrow As Long
    On Error Resume Next
    num = InputBox(Prompt:="Delete Rows?", Title:="Enter the number of Rows", Default:="How Many?")
    If (num < 1) Then
        GoTo Whoops:
    End If
    Dim i As Integer
    i = 1
    Do While i <= num
        Rows(6).EntireRow.Delete
        i = i + 1
    Loop
    GoTo Fin:
Whoops:
        MsgBox ("You have entered an invalid row number")
Fin:
Range("A6").Value = 1
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A7").FormulaR1C1 = "=IFERROR(IF(RC[1]<>"""",R[-1]C+1,""""),"""")": Range("A7:A" & Lastrow).FillDown
    Err.Clear
    On Error GoTo 0
End Sub

[john_cash]

Hi poul
I did tests.
In the attached example I deleted 200 lines and then in cell A6 / A6 the text of cell A is shown

[PaulM100]

You've deleted more rows than the ones containing data and last row stops in B5 now, that's why. Try this, untested:

Sub UserInput()
    Dim num As Integer
    Dim Lastrow As Long
    On Error Resume Next
    num = InputBox(Prompt:="Delete Rows?", Title:="Enter the number of Rows", Default:="How Many?")
    If (num < 1) Or (num > Application.CountA(Range("B6:B"))) Then
        GoTo Whoops:
    End If
    Dim i As Integer
    i = 1
    Do While i <= num
        Rows(6).EntireRow.Delete
        i = i + 1
    Loop
    GoTo Fin:
Whoops:
        MsgBox ("You have entered an invalid row number")
Fin:
Range("A6").Value = 1
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A7").FormulaR1C1 = "=IFERROR(IF(RC[1]<>"""",R[-1]C+1,""""),"""")": Range("A7:A" & Lastrow).FillDown
    Err.Clear
    On Error GoTo 0
End Sub

[john_cash]

Now it does not delete any line always appears

MsgBox ("You have entered an invalid row number")

[PaulM100]

In a cell type =counta(b6:b200000). This should give you the not empty number of cells. In the macro replace b6:b with b6:b200000. Test it by entering a number smaller that the one returned by the formula first and afterwards one bigger.

[john_cash]

i tried:

Option Explicit



Sub UserInput()
    Dim num As Integer
    Dim Lastrow As Long
    On Error Resume Next
    num = InputBox(Prompt:="Delete Rows?", Title:="Enter the number of Rows", Default:="How Many?")
    If (num < 1) Or (num > Application.CountA(Range("B6:B20000"))) Then
        GoTo Whoops:
    End If
    Dim i As Integer
    i = 1
    Do While i <= num
        Rows(6).EntireRow.Delete
        i = i + 1
    Loop
    GoTo Fin:
Whoops:
        MsgBox ("You have entered an invalid row number")
Fin:
Range("A6").Value = 1
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A7").FormulaR1C1 = "=IFERROR(IF(RC[1]<>"""",R[-1]C+1,""""),"""")": Range("A7:A" & Lastrow).FillDown
    Err.Clear
    On Error GoTo 0
End Sub

now in the attach does not delete

[PaulM100]

This one will handle the data. Please be aware that will not delete rows if the user imputs a number higher than the rows with data based on B column data. If you want to delete them and to not add data in column A, as B is empty, then remove this part from the code: Or (num > Application.CountA(Range("B6:B200000")))

Option Explicit

Sub UserInput()
    Dim num As Integer
    Dim Lastrow As Long
    On Error Resume Next
    num = InputBox(Prompt:="Delete Rows?", Title:="Enter the number of Rows", Default:="How Many?")
    If (num < 1) Or (num > Application.CountA(Range("B6:B200000"))) Then
        GoTo Whoops:
    End If
    Dim i As Integer
    i = 1
    Do While i <= num
        Rows(6).EntireRow.Delete
        i = i + 1
    Loop
    GoTo Fin:
Whoops:
        MsgBox ("You have entered an invalid row number")
Fin:
If Application.CountA(Range("B6:B200000")) Then
Range("A6").Value = 1
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A7").FormulaR1C1 = "=IFERROR(IF(RC[1]<>"""",R[-1]C+1,""""),"""")": Range("A7:A" & Lastrow).FillDown
Else: Exit Sub
End If
    Err.Clear
    On Error GoTo 0
End Sub

[john_cash]

Hi poul,
I entered 125 rows
I tried with delete 123 rows
the macro deletes 123 rows but does not contain the formula in A7

[PaulM100]

I've replicated your issue and not there should be no more issues.

Option Explicit

Sub UserInput()
    Dim num As Integer
    Dim Lastrow As Long
    On Error Resume Next
    num = InputBox(Prompt:="Delete Rows?", Title:="Enter the number of Rows", Default:="How Many?")
    If (num < 1) Or (num > Application.CountA(Range("B6:B200000"))) Then
        GoTo Whoops:
    End If
    Dim i As Integer
    i = 1
    Do While i <= num
        Rows(6).EntireRow.Delete
        i = i + 1
    Loop
    GoTo Fin:
Whoops:
        MsgBox ("You have entered an invalid row number")
Fin:
If Application.CountA(Range("B6:B200000")) Then
Range("A6").Value = 1
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A7:A" & Lastrow).FormulaR1C1 = "=IFERROR(IF(RC[1]<>"""",R[-1]C+1,""""),"""")"
'Range("A7:A" & Lastrow).FillDown
Else: Exit Sub
End If
    Err.Clear
    On Error GoTo 0
End Sub

[jolivanes]

Sub Maybe()
Dim a As Long
a = InputBox("How Many Rows Do you want to delete?", "Deleting Rows.")
Rows(6).Resize(a).Delete
    With Range("A6:A" & Cells(Rows.Count, 2).End(xlUp).Row)
        .Formula = "=Row(A1)"
        .Value = .Value
    End With
End Sub

[john_cash]

Poul, I think is now correct.
to be sure I have to try it after the summer holidays.
Greetings and thanks
john

[john_cash]

Hi poul,
I tried to change the inputboxes with userform but here I don't know how to insert num in the inputbox.

num = InputBox(Prompt:="Delete Rows?", Title:="Enter the number of Rows", Default:="How Many?")
    
    UserForm1.Show
    
    If (num < 1) Or (num > Application.CountA(Range("B6:B200000"))) Then 
        GoTo Whoops:

I attach example.
john