macro to delete rows with a condition

[Foxcan]

What I need is a macro to delete a row or rows for a specific range of cells (eg. E20 to E58) when the cells in that range column E is = blank. The cells in column E is formated with a currency sign $. Deleting the row should shift up all rows below. I need to run this macro manually. Excel 2002 SP3. Thanks.

[Leith Ross]

Hello FoxCan,

Welcome to the Forum!

Here is the macro...

Sub DeleteBlanks()

  Dim Rng As Range

    Set Rng = ActiveSheet.Range("E20:E58").SpecialCells(xlCellTypeBlanks)
    If Not Rng Is Nothing Then Rng.EntireRow.Delete xlShiftUp

End Sub

[Foxcan]

Thanks for your quick response. I am rather a novice at this VB stuff. I created a module within VB, copied your info and saved. Went to the spreadsheet and ran the new macro. Results nothing happened. I am sure I missed something and would appreciate your help....Thanks. Piet

[Leith Ross]

Hello FoxCan,

Here is how to install and run the macro...
Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

[Foxcan]

Leith, I should not short change myself. What you told me was what I had already done. But still nothing happens when I run the macro. After further though instead of delete I would like to hide the row. Let me describe the worksheet a little:

Col.A Blank
Col.B Text in cell location
Col.C Text in cell location
Col.D Blank cells
Col.E may have a number (formatted as a currency) e.g $4,000 or nothing e.g $ -
Col. G to N cells may have text, number or be blank

Over all range of cells that are applicable are B20:N52
Macro should do:
pt 1 - criteria for example is if cell E20 has a value ($4.000) then ignore row (don't hide) and go to E21
pt 2 - if cell e21 has no value ($ -) then hide the entire row (a21 to iv21), (move subsequent rows up. )
Now goto E22 repeat pt 1 and pt 2 above till E52.
End

Thanks!

Piet

[Leith Ross]

Hello Foxcan,

Try this macro...

Sub HideRows()

 Dim R As Range
 Dim Rng As Range
 
   Set Rng = Worksheets("Sheet1").Range("E20:E52")
   
     For R = Rng.Rows.Count To 1 Step -1
       If Rng.Cells(R, 1) = 0 Then Rng.Cells(R, 1).EntireRow.Hidden = True
     Next R
   
End Sub

[Foxcan]

Leith, I ran it and received an VB error " compile error: Type Mispatch. Piet

[Leith Ross]

Hello Foxcan,

Sorry, I made a typo. The variable R should be a Long type.

Sub HideRows()
 Dim R As Long
 Dim Rng As Range
 
   Set Rng = Worksheets("Sheet1").Range("E20:E52")
   
     For R = Rng.Rows.Count To 1 Step -1
       If Rng.Cells(R, 1) = 0 Then Rng.Cells(R, 1).EntireRow.Hidden = True
     Next R
End Sub

[Foxcan]

Leith, AWESOME!! Thanks! Piet

[Leith Ross]

Hello Foxcan,

If you feel this has solved your problem, please mark your post to indicate this.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save