Find last empty cell excluding merged cells

[oscarcrous]

Hi

I have used this site for ages now and cannot begin to tell how much I have learned.
But I just cannot find a answer to a VBA problem I have.

I pull a report from a 3rd party in Excel format and make changes to the page to include extra information.

In one section (List Items), I want to find the last used cell in column A, but some of the cells in the section is merged.
The list is always in Column A and the merged cells is two or 3 cells in Column A.

This is my code to find the first and last row of the list, but this method will find the first empty cell even if it is merged.
I need a way to skip any merged cells and find the last empty cell at the bottom of the list, which is never merged, and set that cell as MyLastRow

Dim ListItem As Range
Dim ListItem1st As Range

With Sheets("Sheet1")
Set ListItem = .Cells.Find(what:="List Items")
Set ListItem1st = ListItem.Offset(1, 0)
End With


ListItem1st.Activate
MyFirstRow = ActiveCell.Row
MyLastRow = Range(("A" & MyFirstRow), "A" & Rows.Count).Cells.SpecialCells(xlCellTypeBlanks).Row


Range("A" & MyLastRow).Value = "Em-Power Totals"
Range("A" & MyLastRow).RowHeight = 12



Hope this make sense

 

[Fluff]

Does this do what you want?

Sub chk()
   With Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
      .Value = "Em-Power Totals"
      .RowHeight = 12
   End With
End Sub