Deleting empty lines using a macro

[Dorocica]

Hello,

I have another report which needs to be sorted using a macro. However, before I can sort the data, I need to delete rows at the bottom of each report that have "0" values in column A and B. Again, each report I run is of different length so I need to write a code that will take that into consideration. Here is an example of what I need to do:

Sub second_step()
'
' second_step Macro
'

'
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("7876:7876").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
End Sub

Now, the thing is, not every report will start having "0" values in row 7876. How do I write a code to reflect this, please?

[mikeTRON]

Do you want the code to treat a blank cell as a zero or as a blank?

This will not clear the row if either column A or B is blank:
If Range("A" & i).Formula = 0 And Range("B" & i).Formula = 0 Then
This will clear the row if either is column A or B blank:
If Range("A" & i).Value = 0 And Range("B" & i).Value = 0 Then


The entire code:

Option Explicit

Sub ProcessMyData()

'===================================================================
'Declare Variables
'===================================================================
    Dim wsName As String
    Dim LastRow As Long
    Dim i As Long

'===================================================================
'Define Variables
'===================================================================
    wsName = ActiveSheet.Name 'Defines the SheetName
    LastRow = Worksheets(wsName).Cells(Rows.Count, 1).End(xlUp).Row 'This will find the last used row in column A

'===================================================================
'Do Work
'===================================================================
    For i = 1 To LastRow 'this willl loop through all of the cells
        If Range("A" & i).Formula = 0 And Range("B" & i).Formula = 0 Then 'this will think a blank cell in Bi is NOT zero, is that acceptable?
            Rows(i & ":" & i).ClearContents 'do you want to DELETE the row or just clear it?
        End If
    Next i 'loops to the next i, which moves down one row

'===================================================================
'End Macro
'===================================================================

End Sub

Here is the file I created while creating this:
ClearRowContents.xlsm

[Dorocica]

Hi,

Many thanks for your reply but this didn't work ...

I have some data in columns A-G. Columns A and B then go on to have some zeros in the cells at the bottom of each report. I need to get rid of those "0" from column A and B from the bottom of my report as I then need to sort it.

I really don't understand why the command Ctrl+Shift+arrow down doesn't work???

Thanks!

[Dorocica]

Would an IF THEN command work for this?

Something like IF there is a "0" in column A and B, THEN delete the entire row...

???

[mikeTRON]

Would an IF THEN command work for this?

Something like IF there is a "0" in column A and B, THEN delete the entire row...

???

This is what I had in the code listed above, but I think you were unclear on your expectations. In the sample code you had CLEAR the row, not delete.
Again, it helps a ton to have sample data and sample proposed solution so we can see exactly what you need.

[Dorocica]

Hello,

And sorry. I suppose my lack of knowledge about excel/macros is quite transparent.

I have attached a file that basically shows what I am trying to do. I have data in columns A to G and then "0" going quite a way down in column A and B.

I would like to have a macro that gets rid of all those cells with "0" value.

Thank you!

[JOHN H. DAVIS]

Does this help?

Sub Dorocicazz()
Range("A1:A" & Range("A" & Rows.count).End(3).Row).AutoFilter 1, "0"
Range("A2:A" & Range("A" & Rows.count).End(3).Row).SpecialCells(12).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
Range("B1:B" & Range("B" & Rows.count).End(3).Row).AutoFilter 1, "0"
Range("B2:B" & Range("B" & Rows.count).End(3).Row).SpecialCells(12).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub

[berlan]

Try this:

Sub Clean()
    Application.ScreenUpdating = False
    With Range("A1").CurrentRegion
        .AutoFilter Array(1, 2), 0
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Cheers,
berlan

[Dorocica]

Try this:

Sub Clean()
    Application.ScreenUpdating = False
    With Range("A1").CurrentRegion
        .AutoFilter Array(1, 2), 0
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Cheers,
berlan

Hi,

Thank you. It worked only on column A. When I tried running the same code on column B it didn't work....

[mikeTRON]

Post a sample data set with a proposed solution so we can SEE exactly what you are trying to do, because there is a disconnect between what you are saying and what you actually need, apparently.