Delete rows with cell E empty

[Pekkavee]

I have a macro

Sub DELETING()
For MY_ROWS = Range("E106").End(xlUp).Row To 1 Step -1
If IsEmpty(Range("E" & MY_ROWS)) Then
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
End Sub

Which deletes rows which have cell E empty, between rows number 1 and 106.

Now I want to delete rows which have cell E empty, between rows number 4 and a row number which I give in an InputBoox. It will vary in different situations.

Something like this

Dim strLastrownumber As Integer
strLastrownumber = InputBox("Give last deleted row", "Number")
For MY_ROWS = Range("E" & ????).End(xlUp).Row To 1 Step -1


What do I put in Range("E" & ????).End(xlUp).Row To 1 Step -1
or how can I do this?

Pekka

 

[jaya]

I have a macro

Sub DELETING()
For MY_ROWS = Range("E106").End(xlUp).Row To 1 Step -1
If IsEmpty(Range("E" & MY_ROWS)) Then
Rows(MY_ROWS).Delete
End If
Next MY_ROWS
End Sub

Which deletes rows which have cell E empty, between rows number 1 and 106.

Now I want to delete rows which have cell E empty, between rows number 4 and a row number which I give in an InputBoox. It will vary in different situations.

Something like this

Dim strLastrownumber As Integer
strLastrownumber = InputBox("Give last deleted row", "Number")
For MY_ROWS = Range("E" & ????).End(xlUp).Row To 1 Step -1


What do I put in Range("E" & ????).End(xlUp).Row To 1 Step -1
or how can I do this?

Pekka

Try Range("E"&strLastrownumber)

 

[AlphaFrog]

    Dim Lastrownumber As Long, MY_ROWS As Long 

    Lastrownumber = Application.InputBox("Give last deleted row", "Number", Type:=1)
    If Lastrownumber <= 0 Then Exit Sub  ' User canceled
    For MY_ROWS = Lastrownumber To 4 Step -1
        If IsEmpty(Range("E" & MY_ROWS)) Then Rows(MY_ROWS).Delete
    Next MY_ROWS

 

[Pekkavee]

Jaya, thanks

Your code works fine. ;o)

AlphaFrog, I will test your code as well, thanks.

Pekka

 

[Pekkavee]

Some problems with your Code AlphaFrog.

I will be happy with jaya's.

Thanks both very much for your incredible quick answer.
This is an amaging forum. ;o)

Pekka

 

[Peter_SSs]

Depending on just what you have in your sheet, you may be able to delete them all at once.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DelRows()<br>    <SPAN style="color:#00007F">Dim</SPAN> Lastrownumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> Firstrownumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 4<br>    <br>    Lastrownumber = Application. _<br>        InputBox("Give last deleted row", "Number", Type:=1)<br>    <SPAN style="color:#00007F">If</SPAN> Lastrownumber >= Firstrownumber <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        Range("E" & Firstrownumber & ":E" & Lastrownumber) _<br>            .SpecialCells(xlCellTypeBlanks).EntireRow.Delete<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Pekkavee]

Hi Peter

Beautiful !
That works in a second.

Thanks a lot.

Pekka