If-Then statement with variable range
 

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

--
Message posted from http://www.ExcelForum.com

If-Then statement with variable range
 

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
-----Original Message-----
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 the
cells of a particular column and when it finds the point
when the cell
is empty (IsBlank??, IsEmpty??), I want to select all the
columns and
rows above that point to copy to another location.

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

Can anyone help me with this?

Thanks
Bruce (bjsto2)


---
Message posted from http://www.ExcelForum.com/

.

If-Then statement with variable range
 

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)

"bjsto2 " wrote in message
...
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 the
cells of a particular column and when it finds the point when the cell
is empty (IsBlank??, IsEmpty??), I want to select all the columns and
rows above that point to copy to another location.

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

Can anyone help me with this?

Thanks
Bruce (bjsto2)


---
Message posted from http://www.ExcelForum.com/

If-Then statement with variable range
 

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

"bjsto2 " skrev i meddelandet
...
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 the
cells of a particular column and when it finds the point when the cell
is empty (IsBlank??, IsEmpty??), I want to select all the columns and
rows above that point to copy to another location.

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

Can anyone help me with this?

Thanks
Bruce (bjsto2)


---
Message posted from http://www.ExcelForum.com/

If-Then statement with variable range
 

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))

--
Regards,
Tom Ogilv

--
Message posted from http://www.ExcelForum.com

If-Then statement with variable range
 

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

--
Message posted from http://www.ExcelForum.com

If-Then statement with variable range
 

Typo?

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

instead of

Set rng = Range("A1").CurrentRegion

Regards
Anders Silven


"Tom Ogilvy " skrev i meddelandet
...
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, the
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))

--
Regards,
Tom Ogilvy


---
Message posted from http://www.ExcelForum.com/

If-Then statement with variable range
 

Typo?

Yes

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

should be

Set rng = Range("A1").CurrentRegion

as pointed out by Anders.

--
Regards,
Tom Ogilvy



Tom Ogilvy wrote:
*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, the
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))

--
Regards,
Tom Ogilvy *


---
Message posted from http://www.ExcelForum.com/