• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Empty cell in range

Marc L

Excel Ninja
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)
               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 !​


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


Excel Ninja
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 ...