The donut you wish you had not eaten (limbodog) wrote in excel_juice,
The donut you wish you had not eaten

How to select a range of cells when you don't know how many rows are involved

Okay, the first post here will be a quick macro. It's one of the ones you can't record, you can only make it from scratch. (If you try to record it, you will instead record a macro that selects the exact same bunch of cells every time)

So in keeping with the macro Visual Basic (VB) script, I'll put comments after an apostrophe (') so you can copy and past directly into your VB and still have it work.

Sub Range()

DIM ROWS as string

ROWS = ActiveCell.ROW
Range("A1", "H" & ROWS).Select

end sub

' This macro assumes your data starts at cell "A1" and has a
'pre-determined width out to column "H"
'The variable "ROWS" is created to store a number for us
'The cursor is placed in the "A1" cell.
'The cursor travels down column "A" until the last bit of data
'is found and stops there.
'Our variable "ROWS" stores the number of the row at which it stopped.
'This is how it will know to select the correct number of rows
'and lastly, it selects everything from cell "A1" to column "H" with
'however many rows contain data according to the "ROWS" variable.

(edit: because it is illegible as is)
Tags: range, rows, select, variable
  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

This group is a fabulous idea. I hope to be able to contribute, and I know I will learn.
Hey Stranger! This is Liza. CALL ME! Go Here