VBA wont work due to merged cells

[impressive]

Hello everyone,

I am working on a list where the Area from A1 to Q7 is basically the header (Client Name, Location, Project Start, Project End, etc...), which consists mostly of merged cells.

In this list are items listed that a client gets.

So the listing of the Items starts from A8, and this is also where I want my Macro to work,

I have a macro that should delete empty rows if a Cell in the A-Column is empty.

This is what i have so far:

Sub DeleteRow()

Range("a8:a257").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

When I execute it, I get an error message and in the debug is written:
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

After i removed in my sheet the header area with the merged cells (A1 to Q7) , it worked perfectly fine.

Could someone please help me?

Kind regards

 

[HaHoBe]

Hi,

your code

Sub DeleteRow_SOW()

With Range("a8:a257").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Code posted by me:

With Range("A8:A257")
  If WorksheetFunction.CountBlank(.Cells) > 0 Then
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  End If
End With

I explained before that if no empty cells are within the range the codeline to delete will raise an error. One way to learn VBA is to avoid Select, Selection, Activate as there will be another way to do so (in more that 99% of all cases). These commands are used by the Macro Recorder due to the way the objects are handled there (if recorded).

Holger

 

[James006]

Hi,

Merged Cells are a True Nightmare ...and should almost be Banned ...

However, should you be facing the situation of having to find the First row and the Last Row of a Merged Cell

Sub TestCell()
' To differentiate Merged from Standard Cells
Dim MyMergedCell As Range
Dim FirstRow As Long, LastRow As Long

Set MyMergedCell = ActiveCell.MergeArea
FirstRow = MyMergedCell.Row
LastRow = MyMergedCell.Row + MyMergedCell.Rows.Count - 1

MsgBox "For " & IIf(FirstRow = LastRow, "this Cell ", "this Merged Cell, the Range Is ") & MyMergedCell.Address(0, 0) & _
vbNewLine & vbNewLine & "First Cell Row Number is " & FirstRow & vbNewLine & vbNewLine & "Last Cell Row Number is " & LastRow

End Sub

 

[impressive]

Thank you to all of you.

Now its running.