Empty cell in range

[Marc L]

Using Range.Find method 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 Range.Find demonstration​

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 !​

​

 

[Tom22]

Thanks Marc...finally it is working....Thanks a lot for your help.... cheers

 

[vletm]

Tom22
As written few times ... without any code, You can get those rows as below.

 

[Tom22]

I know Vletm.....I know we can know it by filtering but I was working on large code so don't want anything manual to be done by user or user may forget to look for empty cell...so just want to avoid it

 

[vletm]

Tom22
In any case there have to do something 'manual'
eg with this ... You have three 'different ways' to do it manual
... the most right one is by [ Filter ] by press the button manually
(if press 2nd time, then it will 'hide filtering'. )
Of course, it's Your choice ...

 

[Tom22]

Got your point.....