Range.Findmethod to find out the column header and the column last filled cell is the ultimate way …
In next demonstration codelines #7 to #15 can be replaced by the 3 codelines formula demonstration
but as it seems to need a couple of neurons to maintain it so I stay with a full
in order to work with any worksheet created by Dumb or Dumber (like the movie) so whatever the attachment :
Sub DemoForDumbOrDumberWorksheet() Const T = " Country w/o value :" Dim Rg As Range, R&, S$ Set Rg = ActiveSheet.UsedRange.Find(Split(T)(1), , xlValues, xlWhole, xlByRows) If Not Rg Is Nothing Then With Range(Rg, Columns(Rg.Column).Find("*", SearchDirection:=xlPrevious)) Set Rg = .Find("") If Not Rg Is Nothing Then R = Rg.Row S = Rg.Address(False, False) Set Rg = .FindNext(Rg) While Rg.Row > R S = S & ", " & Rg.Address(False, False) Set Rg = .FindNext(Rg) Wend Set Rg = Nothing MsgBox S, vbInformation, T End If End With End If End Sub
Do you like it ? So thanks to click on bottom right Like !