1

I have recorded a macro and want to select the active cells, however when I select control A while recording the macro it will list the actual cells in my macro, however I want this macro to run on documents with different ranges.

ActiveSheet.Range("$A$1:$AB$6442")

Same again for when I want to skip down to the last line and hit control down arrow it states the row that it goes down on my macro,how can I get this macro to work on spreadsheets which may have more rows?

Selection.End(xlDown).Select
Range("A6443").Select

Many thanks

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Gemma McCaughey
  • 41
  • 1
  • 5
  • 4
    See: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Cyril Aug 13 '18 at 14:41
  • 1
    It would help to know what the range you need is -- not the cells, but how they are chosen. ex.: All cells in the column with "sales tax" in the header. – cybernetic.nomad Aug 13 '18 at 14:41

1 Answers1

1

In addition to avoiding .select/.activate (How to avoid using Select in Excel VBA, which was posted in comments), everythign coems down to HOW you select the range.

A very common code for copying a range is:

Dim lr as long 'lr = last row
lr = cells(rows.count,1).end(xlup).row
range(cells(1,1),cells(lr,1)).Copy

That allows you to programmatically define a fixed range. You could also use a full column or row, such as:

Columns("D").Copy
Columns(4).Copy 'D or 4 are interchangeable

Rows(2).Copy

You can even use a variable, which might determine a row to find based on other conditions. If I want to copy the entire last row:

Dim lr as long 'lr = last row
lr = cells(rows.count,1).end(xlup).row
Rows(lr).Copy

You can even iterate the row in a loop for activities:

Dim lr as long, i as long
lr = cells(rows.count,1).end(xlup).row
For i = 1 to lr
    If cells(i,1).value = 0 Then Rows(i).Interior.Color = RGB(0, 255, 0)
Next i

Knowing how your ranges will be laid out, and using things like .Find or .Match will help select the appropriate rows/columns allowing you to have a more dynamic code, which reacts to changes in the sheet/workbook.

Cyril
  • 6,448
  • 1
  • 18
  • 31