If-Then statement with variable range

[bjsto2]

Hi,

I am a new addition to this forum-thank God it exists!!!

Something I want to do with VBA is to have the code go down through th
cells of a particular column and when it finds the point when the cel
is empty (IsBlank??, IsEmpty??), I want to select all the columns an
rows above that point to copy to another location.

As you can see, the point of selecting the cells above the first blan
may, and probably will, vary each time the routine is called and th
number of rows (but not columns) will vary each time.

Can anyone help me with this?

Thanks
Bruce (bjsto2

 

[Guest]

assuming you are looking at column c

toprow = cells(1,3).end(xldown).row give you the row
number of the top of the range. repeat that to get the
bottom of the range (bottomrow = cells(1,3).end(xldown).end
(xldown).row). select all rows between these and copy to
where you want.

John

 

[Bob Phillips]

cRow s= Cells(Rows.Count,"A").End(xlUp).Row
Range("A1").Resize(cRows,256).Copy Destination:= wherever

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Anders Silven]

Bruce,

If there are no empty columns and the whole row is empty, one easy way is to use
CurrentRegion as in

Range("A1").CurrentRegion.Copy

HTH
Anders Silven

 

[Tom Ogilvy]

If there are no empty rows or columns within the data

Dim rng as Range
set rng = Range("A1").Current.Region


If you have a particular column you want to look at (for example, th
3rd column, column C)

lastrow = Cells(rows.count,3).End(xlup).row

and if row1 can be used to determine the number of columns

lastColumn = cells(1,columns.count).end(xltoLeft).Column

set rng = Range(Range("A1"),Cells(LastRow, LastColumn))

 

[MSP77079]

Lots of options.

To answer your question directly, you could say:
If cell(i,j) = "" then ....

A faster/better way to do this would be to start at the top of th
column and then:
LastRow = Selection.End(xlDown).Row
(you can find this command by turning on the macro recorder, then hol
the Ctrl key and press the down arrow).

Sometimes, a better answer is to use "current region". Turn on th
macro recorder, position the cursor somewhere in your data table, pres
F5 (goto), select Special, then select current region. Most of th
time this will select the entire range that you are interested in ..
it is not fooled by a few blank cells in a data table.

Good luck

 

[Anders Silven]

Typo?

set rng = Range("A1").Current.Region

instead of

Set rng = Range("A1").CurrentRegion

Regards
Anders Silven

 

[Tom Ogilvy]

Typo?

Yes

Set rng = Range("A1").Current.Region

should be

Set rng = Range("A1").CurrentRegion

as pointed out by Anders.