Get the column name

Question

The coding is

dim a,b as double
a = application.workbookfuncation.counta(thisworkbook.sheets(1).Range("A:A"))
b = ThisWorkbook.Sheets(1).Range("A1").CurrentRegion.Columns.Count

concern is that I am unable to get to know that how I select the data from "A1" to columnaddress(b) & a

Kindly suggest be how can I solve it ??

score 1 · Answer 1 · edited May 23 '17 at 12:22

You are doing it incorrectly. That is the wrong way to find the last row and the last column. You may want to see THIS

I have commented the code so you shouldn't have a problem understanding it but if you still do then simply post back :)

Is this what you are trying? (Untested)

Sub Sample()
    Dim ws As Worksheet
    Dim lastrow As Long, lastcol As Long
    Dim rng As Range

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            '~~> Find Last Row
            lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row

            '~~> Find Last Column
            lastcol = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByColumns, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Column

            '~~> Construct your range here
            Set rng = .Range("A1:" & _
                      Split(Cells(, lastcol).Address, "$")(1) & lastrow)

            With rng
                '
                '~~> Do whatever you want to do with the range here
                '
            End With
        End If
    End With
End Sub

Excel Hero · Answer 2 · 2015-09-28T17:40:26.997

There is nothing wrong about using CurrentRegion with regards to A1 as long as you are aware of what it returns.

It returns the contiguous range as defined by row 1 and column 1.

The range ends horizontally at the column immediately to the left of the first truly empty cell in row 1. A zero-length string as the result of a formula is NOT a true blank.

The range ends vertically at the row immediately above the first truly empty cell in column A.

If A1 is empty the CurrentRegion of A1 is A1.

If the above suits your purposes then you do not need the variables in your example at all. You can get the address from A1 to the last row and column of the A1's current region like so:

MsgBox ThisWorkbook.Sheets("Sheet1").[a1].CurrentRegion.Address

.

If you want an object variable then do this:

Set r = ThisWorkbook.Sheets("Sheet1").[a1].CurrentRegion

Now that the entire range is represented by r, you could select it like this:

r.Parent.Activate
r.Select

Note: if using CurrentRegion on any other range besides [A1] the definition of what it returns is more complex because it looks for contiguous data both above and to the left; [A1] has no such areas.

Note: if you need to find the last row and column allowing for truly empty cells in row 1 and/or column A then CurrentRegion will not achieve the desired result. You'll need to use Find.

Get the column name

2 Answers2