Removing blank/empty rows

default_name

Board Regular
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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.

VBA Code:
    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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Are there any columns that will always have data on every row (not formulae)?
 

default_name

Board Regular
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Do you have have formulae in col CA?
Also do you have any other errors, that should not be deleted.
 

default_name

Board Regular
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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:
VBA Code:
 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Replace all that with
VBA Code:
   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

Board Regular
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Replace all that with
VBA Code:
   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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

default_name

Board Regular
Joined
May 16, 2018
Messages
153
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Can you answer my original question from post#2?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,540
Members
416,923
Latest member
jarri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top