2

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

Improve this question
1
  • 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 Commented Jun 1, 2018 at 10:45

5 Answers 5

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

@Jeeped - as far as RowCount is not defined I have decided that it is a compiling error, thus I have improved it :)
2

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

Improve this answer

1 Comment

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.
2

Or just use:

ActiveCell.Resize(RowCount,1).Select

Where 1 is number of columns.

Improve this answer

Comments

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
Improve this answer

Comments

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
Improve this answer

3 Comments

Your cells that define range are not qualified. this
@Jeeped, thanks for the link. I had a interesting read and (tried to) applied it. Correct now?
Yes, that looks a lot better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.