Excel VBA Select Range based on variable

Question

I have a piece of VBA code that is taking data from a spread sheet and formatting it into an input file. This code loops through each column header to makes sure it can find the column its looking for and then offsets by one to get off of the header row and then copies the data to another template.

However this sheet is used by multiple users and the amount of rows being populated can vary so I have set up a variable called rowcount. In this example I'm working on I have 5 records and so I'm trying to select the range from the active cell to the rowcount value (5) but I'm just stuck on the following line:

ActiveSheet.Range(ActiveCell, RowCount).Select

Below is the full code for this section, I know what I'm doing is wrong but any searching via Google throws up results that are too specific and I can't tweak the code to work for me.

If ActiveCell.Value = "Account Name" Then
           ActiveCell.Offset(1, 0).Select

           If ActiveCell.Value <> "" Then
                ActiveSheet.Range(ActiveCell, RowCount).Select
                Selection.Copy
                Sheets("Input").Activate
                ActiveSheet.Range("C2").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                 :=False, Transpose:=False
                Application.CutCopyMode = False
                Worksheets("Account Details").Select
           End If

        End If

For someone with more VBA knowledge I'm sure its easy but I'm essentially trying to get highlight Activecell and down to the variable so in this case A5:A10, copy, then paste.

Thanks in advance

Hey, not really an answer to your question, but on a sidenote: It's generally a good practice to avoid using selectors such as `ActiveCell` `ActiveSheet` or even `.Select` method for that matter.. These commonly cause issue and variety of out of range errors. Try to beware of using them in the future when possible. More info on how to substitute them found [in this stackoverflow question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?noredirect=1&lq=1) — Samuel Hulla, Jun 01 '18 at 10:45

Vityata · Answer 1 · 2018-06-01T10:55:42.113

3

Using Select, Activate and ActiveCell is not considered a good practice in VBA. See How to avoid using Select in Excel VBA

However, it takes time to learn to avoid these. Thus, in your code change this line:

ActiveSheet.Range(ActiveCell, RowCount).Select

To this one:

ActiveSheet.Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column)).Select

And if you have rowCount declared and set correctly, then this is a possible option:

Dim rowCount As Long: rowCount = 5
ActiveSheet.Range(ActiveCell.Column, rowCount).Select

edited Jun 01 '18 at 10:55

answered Jun 01 '18 at 10:33

Vityata

42,633
8
55
100

@Jeeped - as far as `RowCount` is not defined I have decided that it is a compiling error, thus I have improved it :) – Vityata Jun 01 '18 at 10:51

score 2 · Answer 2 · answered Jun 01 '18 at 10:34

2

Or just use:

ActiveCell.Resize(RowCount,1).Select

Where 1 is number of columns.

answered Jun 01 '18 at 10:34

Dan

106
4

score 2 · Accepted Answer · 2018-06-01T10:55:47.107

First yours,

 If ActiveCell.Value = "Account Name" Then
       ActiveCell.Offset(1, 0).Select

       If ActiveCell.Value <> "" Then
            ActiveCell.RESIZE(RowCount, 1).Select   '<~~ resize to the # of rows
            Selection.Copy
            Sheets("Input").Activate
            ActiveSheet.Range("C2").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
             :=False, Transpose:=False
            Application.CutCopyMode = False
            Worksheets("Account Details").Select
       End If

 End If

Now without Select, Activate or ActiveCell

dim c as variant
with worksheets("sheet1")  'you should know what worksheet you are starting out on
    c = application.match("Account Name", .rows(4), 0)
    if not iserror(c) then
        if .cells(5, c).Value <> "" then
            workSheets("Input").Range("C2").resize(RowCount, 1) = _
               .cells(5, c).resize(RowCount, 1).value
        end if
    end if
end with

How to avoid using Select in Excel VBA

Thanks for all the answers but Jeeped I managed to get it working with the resize. Also great advice on not using select etc. I'm intrigued as to why this is not good practice so will do some reading on the link provided. — Carlos80, Jun 01 '18 at 11:27

score 1 · Answer 4 · answered Jun 01 '18 at 10:33

1

At the moment in your range you have just the activecell and row number.

Try something like this:

ActiveSheet.Range(activecell.address &":" &cells(RowCount,ActiveCell.Column).address).select

answered Jun 01 '18 at 10:33

Aneta

149
7

JvdV · Answer 5 · 2018-06-01T11:51:34.310

1

Don't select the range to copy it; implementing something like this should do the job for you:

Sub Test()
Dim RNG As Range
If ActiveCell.Value = "Account Name" Then
    With ActiveSheet
        Set RNG = .Range(.Cells(ActiveCell.Row + 1, ActiveCell.Column), ActiveSheet.Cells(.Cells(ActiveSheet.Rows.Count, ActiveCell.Column).End(xlUp).Row, ActiveCell.Column))
    End With
    RNG.Copy Sheets("Input").Range("C2")
End If
End Sub

edited Jun 01 '18 at 11:51

answered Jun 01 '18 at 11:23

JvdV

70,606
8
39
70

Your cells that define range are not qualified. [this](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) – Jun 01 '18 at 11:32
1

@Jeeped, thanks for the link. I had a interesting read and (tried to) applied it. Correct now? – JvdV Jun 01 '18 at 11:52
1

Yes, that looks a lot better. – Jun 01 '18 at 11:56

Excel VBA Select Range based on variable

5 Answers5