Help with "Range(Selection, Selection.End(xlDown)).Select" that includes empty cell please

[Alroj]

Hi Team,

I am hoping one of you can share your wisdom about solving this puzzle please. I have a dynamic range to select and the range includes empty cells. The problem is that sometimes the range stops once it finds the first empty cell in the column and ignore the data it is supposed to pick up below the empty cell. I have benn using the code below but the section "Range(Selection, Selection.End(xlDown)).Select" does not do the job when it finds an empty cell in the column. I know I can use other methods (e.g. lastRow) but they work with known cells.

Could you please assist me improving this code so it includes empty cell?

    Sheets("Input").Select
  
   Range("A1:J1").Select
    
Selection.Find(What:="Country", After:=ActiveCell, LookIn:=xlFormulas2, _
        Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(1, 0).Select
  
    Range(Selection, Selection.End(xlDown)).Select

Thank you!

 

[Akuini]

I know I can use other methods (e.g. lastRow) but they work with known cells.

What do you mean?

Try this:

Dim c As Range

Set c = Sheets("Input").Range("A1:J1").Find(What:="Country", LookIn:=xlValues, _
lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
 If Not c Is Nothing Then
    Range(c.Offset(1, 0), Cells(Rows.Count, c.Column).End(xlUp)).Select
 End If

 

[Alroj]

Hi Akuini, your solution work perfectly. Thank you very much!!

About your question, I am referring to the scripts that would include: Range("j1:j"&lastRow).select, where lastRow has been defined as lastrow = Range("A" & Rows.Count).End(xlUp).Row

In this situation, it is assumed that the word "COUNTRY" will always be in column "J". However, this is not the case with the data I receive as the headings change order each time. Hence, I have to look for a dynamic way of finding the text ("COUNTRY") first.

Again, I much appreciate your help

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.