Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable in if statement | Excel Worksheet Functions | |||
Variable in Range statement | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming | |||
Case statement in variable range | Excel Programming | |||
Passing a variable to a range statement | Excel Programming |