Looking to DELETE all ROWS in Entire workbook with 8 sheets.

[trekker1218]

Hello,
I need a way yo delete ALL empty rows in an entire workbook with 8 sheets.

I found a few here and they do not seem to work for more than one sheet.

Any help would be great!

Thanks in advance.

Tino

 

[rpaulson]

whats your definition of "Empty Row"? nothing in Column "A"??

 

[trekker1218]

An empty row would be considered anything with a and then the rest of the row empty

 

[My Aswer Is This]

I still don't understand

You said:

anything with a and then the rest of the row empty

To check the entire row to see if it's empty means checking all 16,000 cells in each row.
And there are 1.5 million rows.​

 

[MARK858]

As a bit of a guess at what you want to do....
Test if it does what you want for 1 sheet then if it does I will amend it for multiple sheets.

Sub delit()
    Dim lr As Long, lc As Long, i As Long
    lc = Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column
    lr = Columns(1).Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row

    Application.ScreenUpdating = False

    For i = lr To 2 Step -1
        If WorksheetFunction.CountA(Range(Cells(i, 2), Cells(i, lc))) = 0 Then
            Rows(i).Delete
        End If
    Next

    Application.ScreenUpdating = True
End Sub

 

[trekker1218]

Sorry. The data to check will be a2 to cc2000.
If entire row is empty then delete row.

Then goto next sheet in workbook.

Repeat.

Thank u very much.

 

[MARK858]

Please see post number 5 and see if it does what you want for one sheet or not.

 

[trekker1218]

Will do in morning thank u very much

 

[MARK858]

Will do in morning thank u very much

That being the case you had better have the code for the multiple sheets as I doubt that I will be on the forum at the same time as you.

Sub delit2()
    Dim lr As Long, lc As Long, i As Long, ws As Worksheet
    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            lc = .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column
            lr = .Columns(1).Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row

            For i = lr To 2 Step -1
                If WorksheetFunction.CountA(.Range(.Cells(i, 2), .Cells(i, lc))) = 0 Then ws.Rows(i).Delete
            Next
        End With
    Next

    Application.ScreenUpdating = True
End Sub

 

[trekker1218]

Hello,
The code did not work.

I need the code to remove all empty rows and possibly reset the last cell used. When i press CTRL+END it jumps way past data filled cells to empty cell at row 1000

it needs to cycle through all the sheets in the workbook attached. Sheet names will always be the same if that matters.

If possible to open a file from a specific folder and perform the action above then save and close. That would be fantastic.

thanks again. I know you all provide help for free. THAT IS VERY APPRECIATED.