CODEWITHSUNDEEP
vbaexcelrangecells
aannie
aannie

Reputation: 17

VBA Total range

I want to select entire data (whole range) in the sheet. I dont know why error occurs here:
Range("A1:(Cells(ostatnia_dana, ostatnia_dana1)").Select

Sub ranging()
    Dim ostatnia_dana As Integer
    Dim ostatnia_dana1 As Integer
    ostatnia_dana = Cells(Rows.Count, 2).End(xlUp).Row
    ostatnia_dana1 = Cells(2, Columns.Count).End(xlToLeft).Column
    Range("A1:(Cells(ostatnia_dana, ostatnia_dana1)").Select
End Sub

Upvotes: 1

Views: 517

Answers (1)

Vityata
Vityata

Reputation: 43585

This is a very dirty solution. Change the last row as in my example. :)

Option Explicit

Sub ranging()

    Dim ostatnia_dana As Integer
    Dim ostatnia_dana1 As Integer
    ostatnia_dana = Cells(Rows.Count, 2).End(xlUp).Row
    ostatnia_dana1 = Cells(2, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, 1), Cells(ostatnia_dana, ostatnia_dana1)).Select

End Sub

This is a bit better:

Option Explicit

Sub ranging()

    Dim ostatnia_dana   As Long
    Dim ostatnia_dana1  As Long

    With ActiveSheet
        ostatnia_dana = .Cells(.Rows.Count, 2).End(xlUp).Row
        ostatnia_dana1 = .Cells(2, .Columns.Count).End(xlToLeft).Column
        .Range(.Cells(1, 1), .Cells(ostatnia_dana, ostatnia_dana1)).Select
    End With

End Sub

What is better in the second solution:

In general, it would work quite well without the With ActiveSheet part, but if you refer to another sheet, which is not the active one it would fail. In general, lets imagine that you had some sheet other active and you want to select a sheet named MyWorksheetName. Then the following would work:

Option Explicit

Sub MyRanges()

    Dim ostatnia_dana   As Long
    Dim ostatnia_dana1  As Long

    Dim ws              As Worksheet

    Set ws = Worksheets("MyWorksheetName")

    With ws

        ostatnia_dana = .Cells(Rows.Count, 2).End(xlUp).Row
        ostatnia_dana1 = .Cells(2, Columns.Count).End(xlToLeft).Column

        .Select
        .Range(.Cells(1, 1), .Cells(ostatnia_dana, ostatnia_dana1)).Select

    End With

End Sub

Upvotes: 3

Related Questions