VBA set range for ActiveSheet instead of A1:B2

Question

I'm a noob in VBA I have data range from Row1 to Row 226, trying to export it as csv file in comma delimited.

Sub Comma()

Dim r As Range: Set r = Range("A1:D4") 
Dim buffer As String, delimiter As String, c As Range
Dim i As Long

I want to set the Range to be the current sheet "Sheet1"

When I do Dim r As Range: Set r = Range("Sheet1") , it's throwing out me an error and I can't seem to find what I desire on SO.

Can anyone please help me? Thank you

Are you trying to set the range to be A1:D4 on a different sheet to what it is currently using, or are you trying to set the range to be the entire `UsedRange` of the current sheet? — YowE3K, Jun 02 '17 at 17:33
Entire used range of the current sheet. Regardless how much data I have in the datasheet. Thank you for asking the question. @YowE3K — James T, Jun 02 '17 at 17:35
You need to find the last row and then construct your range. See [This](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) link — Siddharth Rout, Jun 02 '17 at 17:36
Yes - I expect @SiddharthRout is about to post one that avoids `UsedRange` :D — YowE3K, Jun 02 '17 at 17:40
@JamesT - one of the problems of posting questions when there are several highly experienced users who are feeling bored is that you get several good answers! (At the moment Siddharth's answer is the most correct, because it handles both the rows and columns, but it is up to you as to which you tick - both Siddharth and Shai have so many points that they aren't really going to be too upset if they miss out on another 15.) :D — YowE3K, Jun 02 '17 at 17:49

Siddharth Rout · Accepted Answer · 2017-06-02T17:43:43.243

You need to find the last row and then construct your range. See This link

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

    Set ws = Sheets("Sheet1")

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

        Set rng = .Range("A1:D" & lastrow)
    End With
End Sub

and in case your last column is also not fixed then use this

Sub Sample()
    Dim ws As Worksheet
    Dim lastrow As Long, lastCol As Long
    Dim rng As Range
    Dim colName As String

    Set ws = Sheets("Sheet1")

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

            lastCol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column
        Else
            lastrow = 1: lastCol = 1
        End If

        colName = Split(Cells(, lastCol).Address, "$")(1)

        Set rng = .Range("A1:" & colName & lastrow)
    End With
End Sub

@ShaiRado - but both you and Siddharth have forgotten to also find the last used **column** - it may not be `D` :D — YowE3K, Jun 02 '17 at 17:41

Shai Rado · Answer 2 · 2017-06-02T17:40:40.010

2

Try the code below, as good practice always use Option Explicit at the top of your module.

Option Explicit

Sub Comma()

Dim r As Range
Dim LastCell As Range
Dim LastRow As Long

Dim buffer As String, delimiter As String, c As Range
Dim i As Long

With Worksheets("Sheet1")
    ' get dynamic last row
    Set LastCell = .Cells.Find(What:="*", After:=Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    If Not LastCell Is Nothing Then
        LastRow = LastCell.Row ' get last Row with data
    End If

    Set r = Worksheets("Sheet1").Range("A1:D" & LastRow) '<-- set Range to "Sheet1"
End With


End Sub

edited Jun 02 '17 at 17:40

answered Jun 02 '17 at 17:31

Shai Rado

33,032
6
29
51

My follow up is that I have data row that goes all the way to 226 – James T Jun 02 '17 at 17:35
And what I wanted to say in the question is "Regardless how much data I have in the datasheet, I want to assign my datasheet" – James T Jun 02 '17 at 17:36
1

++ yes you did beat me LMAO – Siddharth Rout Jun 02 '17 at 17:40
@SiddharthRout but merely by 5 seconds :) , and you probably typed with your left hand ;) – Shai Rado Jun 02 '17 at 17:41
I'm not going to even try and compete - I'm only half awake! – YowE3K Jun 02 '17 at 17:42

score 0 · Answer 3 · edited Jun 02 '17 at 18:08

0

Try:

Dim r As Range: Set r = Sheets("Sheet1").Range("A1:D4")

edited Jun 02 '17 at 18:08

Siddharth Rout

147,039
17
206
250

answered Jun 02 '17 at 17:31

Alex

1,632
1
12
28

VBA set range for ActiveSheet instead of A1:B2

3 Answers3