VBA to hide empty rows within non-contiguous named range

[thefordness]

I need a code to run through cells in a named range and remove the blank rows. I have one named range that actually refers to several smaller named ranges on separate parts of a sheet. Ideas?

 

[JLGWhiz]

I need a code to run through cells in a named range and remove the blank rows. I have one named range that actually refers to several smaller named ranges on separate parts of a sheet. Ideas?

How non-contiguous are the smaller ranges. I'm thinking that if you delete a bunch of rows in the overall range, it will compress the smaller ranges and could cause some overlap of the named ranges as the rows shift to replace the deleted ones Or maybe not, need to think on this a while.

 

[JLGWhiz]

How non-contiguous are the smaller ranges. I'm thinking that if you delete a bunch of rows in the overall range, it will compress the smaller ranges and could cause some overlap of the named ranges as the rows shift to replace the deleted ones Or maybe not, need to think on this a while.

It seems to work OK without destroying the integrity of the named ranges. You can use the code below by simply changing the row numbers for the value of i (red font). The code works from bottom to top, hence the last row first to the first row last. Be sure to test this on a copy first, because the undo button will not reverse deletions made by code.

Sub doblanks()
For i = 41 To 2 Step -1
    If Cells(i, 2) = "" Then
        Rows(i).Delete
    End If
Next
End Sub

 

[mgd_Excel]

if your still looking for a solution try this.

Sub del_blank_rows_1()
' assumption: User inputs a named range
Dim rinput As Range, rngArea As Range, no_of_areas As Integer, rname As String
Dim rngArray() As String, intArea As Integer, intCol As Integer, intRow As Integer, rowBlank As Boolean
Dim rngRowToDelete As Range, intLastRow As Integer

On Error GoTo cancel_trap

Set rinput = Application.InputBox("Input Range Name (=Range Name)", "Input range", "A1", Type:=8)

' Check how many areas are in the named range
' non_cont_range
no_of_areas = rinput.Areas.Count

If no_of_areas >= 1 And rinput.Cells.Count > 1 Then

ReDim rngArray(1 To no_of_areas)

' split only return a o based array regardless of the setting of Option Base
rngArray = Split(rinput.Address, ",")

For intArea = 0 To no_of_areas - 1

Set rngArea = Range(rngArray(intArea))
'Position the active cell
rngArea.Select

'Select the first row in the area
Set rngRowToDelete = Selection.Resize(1)
rngRowToDelete.Select
'if we delete any rows we will be modifying the original range
'so we need to keep track of where the last row is
intLastRow = rngArea.Rows.Count
For intRow = 1 To intLastRow
rowBlank = True
For intCol = 1 To rngArea.Columns.Count
If rngRowToDelete.Cells(1, intCol) <> "" Then
rowBlank = False
Exit For
End If
Next

If intRow <= intLastRow And rowBlank Then

rngRowToDelete.Delete Shift:=xlUp
intLastRow = intLastRow - 1

End If

Set rngRowToDelete = Selection.Offset(1, 0)
rngRowToDelete.Select

Next

Next

End If
cancel_trap:
End Sub