Excel VBA Deleting Blank Rows

[RajiSingh]

Hi Guys

I am trying to delete blank rows from my worksheet.
The code works but I wanted to start this 5 rows down (This deletes my header)
Could you please help?

Thanks R


Sub d()


Dim rngBlanks As Range
Dim i As Integer
For i = 2 To 3
On Error Resume Next
Set rngBlanks = Columns(i).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rngBlanks Is Nothing Then
rngBlanks.EntireRow.Delete
End If
Next


End Sub

 

[sunny102]

use the following code


Dim i As Long



'We turn off calculation and screenupdating to speed up the macro.

With Application

.Calculation = xlCalculationManual

.ScreenUpdating = False



'We work backwards because we are deleting rows.

For i = Selection.Rows.Count To 5 Step -1

If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then

Selection.Rows(i).EntireRow.Delete

End If

Next i



.Calculation = xlCalculationAutomatic

.ScreenUpdating = True

End With

 

[mrmmickle1]

Your code currently deletes all rows in Column B with blanks and then moves to column C and repeats the process. Is this what you are trying to do? If your Headers in column B or C have text then they should not be getting deleted. I just ran a few tests and my Headers remained after the sub procedure has run.

Can you please provide a small sample of your data and possibly demonstrate your end goal by providing a before and after example?

 

[Joe4]

Try this:

Sub d()

    Dim rngBlanks As Range
    Dim delRange As Range
    Dim i As Integer

    For i = 2 To 3
        On Error Resume Next
        Set rngBlanks = Columns(i).SpecialCells(xlCellTypeBlanks)
        Set delRange = Intersect(rngBlanks, Rows("5:" & Rows.Count))
        On Error GoTo 0
        If Not rngBlanks Is Nothing Then rngBlanks.EntireRow.Delete
    Next

End Sub

But as Matt pointed out, because you are doing columns B and C independently, it might not actually do what you want. That part may be a bit problematic, depending on what your data looks like, and what you actually want it to do.

 

[RajiSingh]

Hey!!!

Thanks for the help

This is a sample of my report

Portfolio ClassSec AsOf - Run on 12/20/2016
As Of Date: 7/31/2016 Class Scheme: Region Group Scheme Return Type: GROSS OF FEES
Report Measure: Return Currency: BAS Annualized: Yes
Long/Short: Both Portfolio Filter: MONTHLY REPORTS
Class/Security	End Weight	Ending Market Value	End Date
x portfolio	100.00	988,837,064.87	7/31/2016
Cash & Equivalents	5.50	54,338,079.41	7/31/2016
Cash Only	3.41	33,754,571.88	7/31/2016
Equivalents Only	2.08	20,583,507.53	7/31/2016
Equity Total	94.50	934,498,985.46	7/31/2016
Pacific	13.88	137,216,969.95	7/31/2016
Australia	0.95	9,358,646.37	7/31/2016
Hong Kong	3.86	38,202,614.50	7/31/2016
Japan	6.13	60,574,992.54	7/31/2016
Singapore	2.94	29,080,716.54	7/31/2016
Europe	30.06	297,283,076.78	7/31/2016
Euro	11.20	110,791,725.95	7/31/2016
Finland	0.84	8,334,253.15	7/31/2016
France	2.48	24,502,151.39	7/31/2016
Germany	1.88	18,561,419.04	7/31/2016
Greece			7/31/2016
Holland	2.91	28,786,398.18	7/31/2016
Ireland	1.71	16,957,974.38	7/31/2016
Italy	0.22	2,143,618.38	7/31/2016
Spain	1.16	11,505,911.43	7/31/2016
Denmark			7/31/2016
Non Euro	18.86	186,491,350.83	7/31/2016
Norway	1.46	14,436,141.28	7/31/2016
Switzerland	5.91	58,419,077.07	7/31/2016
United Kingdom	11.49	113,636,132.48	7/31/2016
Emerging Markets	9.26	91,529,814.60	7/31/2016
Brazil	0.99	9,838,373.22	7/31/2016
China	0.92	9,065,659.41	7/31/2016
Hungary			7/31/2016
India	2.25	22,234,014.90	7/31/2016
Korea	2.66	26,278,319.85	7/31/2016
Malaysia			7/31/2016
Mexico			7/31/2016
Panama	0.65	6,395,582.82	7/31/2016
South Africa	1.79	17,717,864.40	7/31/2016
North America	41.31	408,469,124.13	7/31/2016
United States	41.31	408,469,124.13	7/31/2016

<tbody>
</tbody>

I did not want to include the header.. but pretty much any country that does not have a end weight and market value, delete the row

 

[Joe4]

Is it always the case where they are either missing BOTH End Weight and Ending Market Value or neither?
In other words, it is ever possible to only be missing one of these fields?

If not, there is no need to check both columns, you would only need to check one.

 

[RajiSingh]

no need to check both

 

[Joe4]

This should do that without any need for loops (which can slow down procedures and should be avoided, if possible):

Sub d()

    Dim delRange As Range

    'Identify all blank cells in 2nd column from row 5 on down and delete them
    Set delRange = Intersect(Columns(2).SpecialCells(xlCellTypeBlanks), Rows("5:" & Rows.Count))
    If Not delRange Is Nothing Then delRange.EntireRow.Delete

End Sub

 

[RajiSingh]

wow this works perfectly!

Thanks Joe!

 

[Joe4]

You are welcome!