Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Macro fro removing blank cells in a range

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    137
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Macro fro removing blank cells in a range

    Hi all, I have found the below macro to remove blank rows in a sheet. I run it, however it does not remove the blank rows (but tells me that it has).
    I think I need to set the range. I have added a Range called 'Blank_Cells' but I don't know how I add this into the code, can anybody please help?
    Thanks.

    Sub DeleteBlankRows()
    Dim Rw As Range
    If WorksheetFunction.CountA(Selection) = 0 Then
    MsgBox "No blank rows found", vbOKOnly
    Exit Sub
    End If
    With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    Selection.SpecialCells(xlCellTypeBlanks).Select
    For Each Rw In Selection.Rows
    If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
    Selection.EntireRow.Delete
    End If
    Next Rw
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    End With
    MsgBox "Blank Rows Removed"
    End Sub

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,417
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro fro removing blank cells in a range

    .
    The macro code is needing you to highlight a range first:

    Selection.SpecialCells(xlCellTypeBlanks).Select

    "Selection" is the indicator for you to select a range first.

    I just numbered Col A .... A1, A2, A3 ..... A26, A27

    I made A11 blank ... highlighted A1:A27 and ran the macro. It deleted Row 11 from the Col.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •