Deleting rows

[azizrasul]

I have data in 3 columns of about 500 rows (but the number of rows can vary).

I want to delete all the rows where there is a 0 or "" in any cells of columns B or C using VBA code.

 

[Joe4]

This should do that:

Sub MyDeleteRows()
 
    Dim myLastRow As Long
    Dim myRow As Long
 
    Application.ScreenUpdating = False
 
'   Find last row
    myLastRow = Range("A1").SpecialCells(xlLastCell).Row
 
'   Loop through all rows deleting rows where columns B or C equal 0 (including null values)
    For myRow = myLastRow To 1 Step -1
        If (Cells(myRow, "B") = 0) Or (Cells(myRow, "C") = 0) Then
            Rows(myRow).EntireRow.Delete
        End If
    Next myRow
 
    Application.ScreenUpdating = True
 
End Sub

 

[azizrasul]

Joe4, I couldn't get you code to work, but using your 'template' did the following which made it work.

    myLastRow = Sheets("Sheet1").UsedRange.Rows.Count
 
    'Loop through all rows deleting rows where columns B or C equal 0 or "".
    For myRow = myLastRow To 1 Step -1
        If Sheets("Sheet1").Cells(myRow, 2) = 0 Or Sheets("Sheet1").Cells(myRow, 3) = 0 Or Trim(Sheets("Sheet1").Cells(myRow, 2)) = "" Or Trim(Sheets("Sheet1").Cells(myRow, 3)) = "" Then
            Sheets("Sheet1").Rows(myRow).EntireRow.Delete
        End If
    Next myRow

 

[Joe4]

Joe4, I couldn't get you code to work, but using your 'template' did the following which made it work.

Sounds like there were some important details you may not have mentioned (where you are placing the code, whether or not it is being run from the same sheet, what is actually in the cells, are they derived from formulas or cut and pasted values, etc).

Whatever the case may be, I'm glad it got you started down the right path and you were able to adapt it to work for you.

 

[azizrasul]

Is there a way of changing the code so that I only have to mention Sheet1 once and the remaining code still works?

Thanks for your help. Much appreciated.

 

[Weaver]

See the use of 'with' below:

    [B]With Sheets("sheet1")
[/B]       myLastRow = .UsedRange.Rows.Count
    
       'Loop through all rows deleting rows where columns B or C equal 0 or "".
       For myRow = myLastRow To 1 Step -1
           If .Cells(myRow, 2) = 0 Or .Cells(myRow, 3) = 0 Or Trim(.Cells(myRow, 2)) = "" Or Trim(.Cells(myRow, 3)) = "" Then
               .Rows(myRow).EntireRow.Delete
           End If
       Next myRow
    [B]End With[/B]

 

[Joe4]

What kind of module are you placing the code in?
A standard module, or a Worksheet module?

If you place it in a standard module and run it while the sheet you want to apply it to is active, you shouldn't need any of the sheet references (like my original code). It will just run on the active sheet.

You could also try just putting:
Sheets("Sheet1").Activate
at the very beginning of the code (once again, assuming you are NOT placing the code in a Worksheet module but a standard one).

 

[azizrasul]

Tried the With...End With which did the job.
The code is behind a label which is on a sheet called 'Dashboard'.
Sheet1 is 'imported' in.

 

[Joe4]

What I was referring to is that there are different kinds of modules in VB, there are standard or general modules, worksheet modules, workbook modules, etc. Which module you place your code in can affect how it will run and how it needs to be written.

Chip Pearson did a nice write-up on this here: http://www.cpearson.com/excel/codemods.htm

 

[azizrasul]

OK Joe4, I didn't realise that. Thanks for the link. I obviously need to brush up on this. Thanks for your help on this thread.