CODEWITHSUNDEEP
excelvba
CurveGamma
CurveGamma

Reputation: 4559

Select all data in sheet

I am writing a macro that opens a New Workbook and copies all data to a different Workbook. The data always starts on C12 but we dont always know how many rows of data there are

The following code is giving an error:

Workbooks("Somesheet.xls").Activate
Sheets("Sheet1").Activate
 With Range("C12").Select
 End (xlDown)
 End With

How do I select all rows from C12?

Upvotes: 2

Views: 4132

Answers (3)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

In order to select all data from cell C12 down, use the UsedRange property to find the absolute last row used. Other methods will stop before the true end of the sheet if there is a blank cell in Column C. This snippet sets a Range variable that spans from C12 to the last used cell in the sheet:

Dim rng As Range
Dim lRowLast As Long, lColLast As Long
With ActiveWorkbook.Worksheets("Sheet1")
    lRowLast = .UsedRange.Row + .UsedRange.Rows.Count - 1
    lColLast = .UsedRange.Column + .UsedRange.Columns.Count - 1
    Set rng = .Range(.Range("C12"), .Cells(lRowLast, lColLast))
End With

Note: use .Row + .Rows.Count - 1 to handle cases where the used range starts after the first row.

Edit: Updated example to fix the bug that @brettdj pointed out.

Upvotes: 1

brettdj
brettdj

Reputation: 55672

I use Find to detect the true last used cell as UsedRange can be unreliable with over- estimating the extent of the true used range unless it is forced to recalc in the code before being used. UsedRange can also be problematic unless it start from A1 (I had this issue with Rachel's code when testing data only in C12:C40, the answer provided was G34:G60)

From your question sample code it appears you only wanted column C data from C12 down (which is what this code does). It can readily be extended accross the true used range of columns, or as entire rows if needed 

Sub GetData()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Set wb = Workbooks("SomeSheet.xlsm")
    Set ws = wb.Sheets("Sheet1")
    Set rng1 = ws.Columns("C").Find("*", ws.[c1], xlFormulas, , , xlPrevious)
    If rng1.Row > 12 Then
        Set rng2 = ws.Range(ws.[c12], rng1)
        MsgBox "Your range is " & rng2.Address(0, 0)
    Else
        MsgBox "Last row in " & ws.Name & " was only " & rng1.Row
    End If
End Sub

Upvotes: 1

iDevlop
iDevlop

Reputation: 25262

dim rng as range
with Workbooks("Somesheet.xls").Sheets("Sheet1").range("C12")
    set rng = range(.cells(0,0), .end(xldown))
end with

You could also use 

set rng = Workbooks("Somesheet.xls").range("C12").CurrentRegion

Upvotes: 2

Related Questions