firstcolumn finalrow selection

Question

I am trying to code a sub procedure that captures the first cell, first column and selects the data unto the last row, last column value. I would like to call and reference this sub in other sub procedures. I am looking for an effective coding method.

I have spent time with these two answers:

How to select the lastrow

Sub SelectLastRow()
    Dim nRow As Long, nColumn As Long
    nRow = Cells(Rows.Count, "A").End(xlUp).Row
    nColumn = Cells(nRow, Columns.Count).End(xlToLeft).Column
    Range(Cells(nRow, "A"), Cells(nRow, nColumn)).Select
End Sub

I am interested in reversing the higher voted answer here: Excel VBA select range at last row and column

Here is a data set, I just want to capture the whole selection with headers.

Fail Count  
2    90
1    58

Edit:

I just found this code as well, which does what I desire, what is the significance of .SpecialCells(xlCellTypeConstants, 23)?

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("a1:G" & LR).SpecialCells(xlCellTypeConstants, 23).Select

score 1 · Answer 1 · answered Dec 16 '14 at 01:34

1

Public Function DataRange(Somesheet as String, Optional StartCell as String) as Excel.Range
    If StartCell = "" Then
       DataRange =ThisWorkbook.Sheets( Somesheet).Range( ThisWorkbook.Sheets( Somesheet)_ 
                  .Cells(1,1).End(xldown),ThisWorkbook.Sheets(Somesheet).Cells(1,1).End(xlright))
    Else
       DataRange = ThisWorkbook.Sheets(Somesheet).Range(ThisWorkbook.Sheets(Somesheet)_
                   .Range(Startcell).End(xldown),ThisWorkbook.Sheets(Somesheet).Range(Startcell).End(xlright))
    End If

End Function

If you put in a cell reference, it will return a data range around that cell.

answered Dec 16 '14 at 01:34

cronos2546

1,088
7
16

You will need to give it the sheet that the data is on. – cronos2546 Dec 16 '14 at 01:35
How could the code be edited to stay inside a module? Is this not recommended, per you answer? – phillipsK Dec 16 '14 at 01:38
Could you please give an example `DataRange(ActiveSheet).Select` does not work and if my sheet is titled `5555` nor does `DataRange(5555).select' work. I keep receiving application defined error or the function does not support the `.select` method? – phillipsK Dec 16 '14 at 01:51
So you would need to put in a range variable SomeRange = DataRange(ActiveSheet.Name) or DataRange("5555") then SomeRange.Select – cronos2546 Dec 16 '14 at 03:41
Sheet2 has been renamed to "2" and while attempting to call the function `DataRange ("2")` this line of code receives application/object defined error `DataRange = ThisWorkbook.Sheets(Somesheet).Range(ThisWorkbook.Sheets(Somesheet).Cells(1, 1).End(xlDown), ThisWorkbook.Sheets(Somesheet).Cells(1, 1).End(xlRight))` – phillipsK Dec 16 '14 at 13:46

score 1 · Answer 2 · answered Dec 16 '14 at 01:44

what is the significance of .SpecialCells(xlCellTypeConstants, 23)?

It is the default argument and could be omitted:

XlSpecialCellsValue constants Value 
xlErrors                       16 
xlLogical                       4 
xlNumbers                       1 
xlTextValues                    2

firstcolumn finalrow selection

2 Answers2