CODEWITHSUNDEEP
excelvba
DataScientist
DataScientist

Reputation: 67

Selecting entire column with blank cells with alternate empty cells

I want to select the entire column that contain the names to copy it knowing that the Name column number can change between a spreadsheet and another and there is some blank cells in the middle (missing value)

Suppose that the Names column is the column B, so if I want to select the entire column even with the some blank cells in the middle I can use the following code:

Range("B2", Range("B" & Rows.Count).End(xlup)).Select

But the Name column number is variable. so i tried to make it like this :

Sub ColSelection ()

Dim NameHeader As range 

'To select the header of Name column 

Set NameHeader = ActiveSheet.UsedRange.Find("Name")

ActiveSheet.Range(NameHeader.Offset(1,0), Range(NameHeader & Rows.Count).End(xlUp)).select 

' run tim error 1004 " Methode 'Range' of object '_Global' Failed 

End sub

I guess that I have to replace the second NameHeader with his column address. how to do that? Should I set a Var to store the address of the range NameHeader, and use it. If it's the case how should I set this var, I mean as long or as Variant..ect?

Thanks :)

Upvotes: 0

Views: 758

Answers (3)

user12605123
user12605123

Reputation: 1

Sub test1a()

Dim NameHeader As Range
Set NameHeader = ActiveSheet.UsedRange.Find(InputBox("HEADER"))
If Not NameHeader Is Nothing Then
ActiveSheet.Range(NameHeader.Offset(1), Cells(Rows.Count, NameHeader.Column).End(xlUp)).Select
End If

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149297

Is this what you are trying? I have commented the code so you should not have a problem understanding it. But if you still do then post your query.

Is this what you are trying?

Sub ColSelection()
    Dim NameHeader As Range
    Dim ws As Worksheet
    Dim ColName As String
    Dim LRow As Long
    Dim rng As Range

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        Set NameHeader = .UsedRange.Find("Name")

        '~~> Check if we found the text
        If Not NameHeader Is Nothing Then
            '~~> Get the Column Name
            ColName = Split(Cells(, NameHeader.Column).Address, "$")(1)

            '~~> Get the last row in that range
            LRow = .Range(ColName & .Rows.Count).End(xlUp).Row

            '~~> Construct your range
            Set rng = .Range(ColName & NameHeader.Row & ":" & ColName & LRow)

            With rng
                MsgBox .Address
                '~~> Do whatever you want with the range
            End With
        End If
    End With
End Sub

Screenshot

Unable to upload an image, Imgur is rejecting images for the time being. Will update it directly later.

http://prntscr.com/kftsad

Upvotes: 3

BigBen
BigBen

Reputation: 49998

No need to look at NameHeader.Address - just use its .Column - something like this (noting that you don't have to Select a Range before copying.

Sub GrabNameCol()
    Dim NameHeader As Range
    Set NameHeader = ActiveSheet.UsedRange.Find("Name")

    If Not NameHeader Is Nothing Then
        ActiveSheet.Range(NameHeader.Offset(1), Cells(Rows.Count, NameHeader.Column).End(xlUp)).Select ' or just .Copy
    End If

End Sub

Upvotes: 1

Related Questions