VBA Select Non-Contiguous Column

[Jdags]

Hi,

I'm new to the forum, usually I've been able to search and find solutions but this time I'm at a loss.
I'm trying to select to the last cell with a value in a non-contiguous column without using a direct column reference. I'm using a downloaded report and sometimes upper management adds or moves columns so I use find to locate the column I want to select.
I want to do essentially what this utility does:

http://www.asap-utilities.com/blog/index.php/2013/02/06/tip-quickly-extend-your-selection-down-without-scrolling/

Here is what I have:

Sub Format_EIB()
Cells.Find(What:="Ledger Account - Reference ID", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Select
        Selection.Offset(1, 0).Select
            Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
Sub Format_EIB()
Cells.Find(What:="Ledger Account - Reference ID", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=True, SearchFormat:=False).Select
        Selection.Offset(1, 0).Select
            Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

However "Range(Selection, Selection.End(xlDown)).Select" stops the first time it finds a blank.
I figure this could be achieved by taking a count of an adjacent column that is always contiguous or using "Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select" and having it count from the start column to the end then back tracking to leave only one column selected. But I haven't had any luck figuring out the proper syntax to achieve this.
Or does someone have another idea on how to achieve this?
If I wasn't really clear please let me know.
Any help would be very appreciated!
Thank you for taking the time to read over this!

 

[Roderick_E]

This will typically get the last row regardless of column.
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

 

[Jdags]

I'm having trouble integrating the code
Do I need to Dim lastrow as something?
The intent is for it to select all the cells below the column header down to the last populated cell in the column.
Like the utility does on this site:
ASAP Utilities for Excel – Blog » Tip: Quickly extend your selection down to the end without scrolling

 

[Joe4]

I'm trying to select to the last cell with a value in a non-contiguous column without using a direct column reference.

The intent is for it to select all the cells below the column header down to the last populated cell in the column.

If I wasn't really clear please let me know.

I am afraid it isn't quite clear what you want, at least not to me.
If you do not know which column you want, how can you figure our which column to go find the last populated cell in?

Perhaps if you can post a few simple examples of what your data looks like with your expected results, it it will be more clear.

There are instructions on how you can post images found in the "Posting Aids" section of this link here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. There is also a "Test Here" forum on this site that you can use to test those tools before using them in your threads.

 

[Dan Waterloo]

Maybe this is what you're looking for Jdags?

'Find the last row on the worksheet based on the nth column.

nLastRow = ws.Cells(Application.Rows.Count, n).End(xlUp).Row

 

[MARK858]

@Jdags, do you mean something like the below?

Sub Format_EIB()
    Dim x As Range, lstRw As Long
    
    Set x = Cells.Find(what:="Ledger Account - Reference ID", After:=Cells(1, 1), LookIn:=xlValues _
                                                                                          , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
    
    lstRw = Columns(x.Column).Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
    Range(Cells(2, x.Column), Cells(lstRw, x.Column)).Select
End Sub

 

[Jdags]

@MARK858: That's exactly what I was looking for! Thank you so much that's perfect!

@Joe4: Sorry that it was confusingly worded. Basically, my "Ledger Account - Reference ID" won't always be in Column C so I didn't want a marco that would reference Column C directly. But, rather, would search for the column header "Ledger Account - Reference ID" and select it, then proceed from that selection to select the data below it.