Removing blank/empty rows

[default_name]

I have a workbook that contains several different sheets of data.
Two sheets, in particular, occasionally end up being unnecessarily long at the bottom/end of the sheet.
There are many instances where the blank rows extend well beyond the data.
This has increased my file size dramatically, making it much more difficult to work with.

I am trying to implement code that will go through each of these worksheets, and remove all of the blank rows that are coming after the data.

Here is what I have right now. I am thinking that there must be a more simple way to do this, because when I run this code it takes forever to run, and it usually ends up crashing Excel in the end.

    Sheets("Data1").Select
    Dim x As Long
    With ActiveSheet
        For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
            If WorksheetFunction.CountA(.Rows(x)) = 0 Then
                ActiveSheet.Rows(x).Delete
            End If
        Next
    End With
  
    Sheets("Data2").Select
    Dim y As Long
    With ActiveSheet
        For y = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
            If WorksheetFunction.CountA(.Rows(y)) = 0 Then
                ActiveSheet.Rows(y).Delete
            End If
        Next
    End With

Thanks in advance

 

[Fluff]

Are there any columns that will always have data on every row (not formulae)?

 

[default_name]

Are there any columns that will always have data on every row (not formulae)?

I just checked.
I noticed that a lot of cells in column CA have something. (didnt notice that before).

I guess I've been going about this all wrong. My question should be more like:

If the cell in column CA is either blank for some reason or says #REF! then I'd want to delete the entire row.

 

[Fluff]

Do you have have formulae in col CA?
Also do you have any other errors, that should not be deleted.

 

[default_name]

Do you have have formulae in col CA?
Also do you have any other errors, that should not be deleted.

There are no formulas in column CA.
The data in CA has been pasted over from another worksheet (worksheet is called #label). Perhaps the issue can be fixed if the copy routine from the #label worksheet to the data sheets is fixed.

I am currently copying over from the #label worksheet like this:

 Sheets("#label").Select
    Columns("F:F").Select
    Selection.Copy
    Sheets("Data1").Select
    Range("CA1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Range("CA1").Select

Is there a way to modify this code so that it ignores all the blank valued cells. Right now it copies over all the values in column F, and pastes all of the blank values as #REF!

Thank you for helping me walk through this issue to the root of the actual problem, Fluff!

 

[Fluff]

Replace all that with

   Dim Rng As Range
   With Sheets("#label")
      Set Rng = .Range("F1", .Range("F" & Rows.Count).End(xlUp))
   End With
   Sheets("Data1").Range("CA1").Resize(Rng.Count).Value = Rng.Value

 

[default_name]

Replace all that with

   Dim Rng As Range
   With Sheets("#label")
      Set Rng = .Range("F1", .Range("F" & Rows.Count).End(xlUp))
   End With
   Sheets("Data1").Range("CA1").Resize(Rng.Count).Value = Rng.Value

Thanks Fluff!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[default_name]

Ok, the copy error has been fixed.
But I am still having a blank cell issue.
Even though my data ends where it should now, the worksheet still continues to grow beyond my data.

Back to my original problem/question for this thread.
How can I go to the bottom of my data and remove all of the seemingly blank rows beneath it?
(similar to going to the bottom of my data, selecting the entire row beneath it, doing Ctrl+Shift+Down to select all the rows beneath the data, then right-click and Delete to actually remove all the rows below)
Doing this manually solves my problem....but how can I write that routine in VBA? I tried recording it, but that doesnt work out so well since the 'bottom of my data' varies.

 

[Fluff]

Can you answer my original question from post#2?