using VBA to find the last row of a merged cell

Question

I am trying to use find the last row of a merged cell with text and hide all rows beside that

For example:

A1:A5 is a merged cell with text "A", A6:A10 is a merged cell with text "B", etc

I want to write a code that would find the last row of the merged cell with text "B", and would hide any rows above or below the merged cell.

At the moment I am defining the rows to hide manually, but these change frequently so my method is not very efficient.

Any suggestions on how to find the last row instead?

Sub FindLastRow() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Test") 'Hide all rows above B ws.Rows("1:5").EntireRow.Hidden = True 'Hide all rows below B ws.Rows("11:80").EntireRow.Hidden = True End Sub

I've never used that before, could you give an example of how I could incorporate it into my code? — eenz
– eenz, Commented Oct 22, 2020 at 14:49
Looks like you have an answer already with Range.Find, which accomplishes the same thing. — BigBen
– BigBen, Commented Oct 22, 2020 at 15:02

JvdV · Accepted Answer · 2020-10-22 14:49:40Z

I guess you could try the following, making use of Range.Find and Range.MergeArea:

Sub Test() Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Test") Dim fullRng As Range, fndRng As Range Set fullRng = ws.Range("A1:A80") Set fndRng = ws.Range("A1:A80").Find(What:="B", Lookat:=xlWhole) If Not fndRng Is Nothing Then fullRng.Rows.Hidden = True fndRng.MergeArea.Rows.Hidden = False End If End Sub

Collectives™ on Stack Overflow

using VBA to find the last row of a merged cell

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related