Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
one way
Dim ws As Worksheet Dim lastrow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For Each Cell In ws.Range("B2:B" & lastrow) If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
This should help you find your last cell
http://www.beyondtechnology.com/geeks012.shtml How to select B2 to end for ActiveSheet. Range("B2:" & LastCell(ActiveSheet).Address).Select If you wish to name the range selected: Sub NameSelection() Const myRangeName = "Test" ActiveWorkbook.Names.Add Name:=myRangeName, RefersTo:=Selection End Sub -- Steve "Jimbo213" wrote in message ... I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
Gary: Your code (Rows.Count, "B").End(xlUp).Row and ("B2:B" & lastrow) confuses me. Let's say the spreadsheet is 6 columns wide [A to F] and 10 rows high. And I want Col A:A and Row 1:1 excluded I want to name the range "B2:F10" - that is where the For-Each cells are. It looks to me that your code will return the range B2:B10 Am I missing something? Thanks for your reply & assistance. Jimbo213 "Gary Keramidas" wrote: one way Dim ws As Worksheet Dim lastrow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For Each Cell In ws.Range("B2:B" & lastrow) If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
didn't realize you wanted more than 1 column.
you can use a formula from rick to help out. it will find the column with the data in the highest numbered row check it out and see if it helps. Dim ws As Worksheet Dim LastUsedRow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") LastUsedRow = ws.Columns("B:F").Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row For Each Cell In ws.Range("B2:F" & LastUsedRow) If Not IsEmpty(Cell) Then ' code for each cell here End If Debug.Print Cell.Address Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Gary: Your code (Rows.Count, "B").End(xlUp).Row and ("B2:B" & lastrow) confuses me. Let's say the spreadsheet is 6 columns wide [A to F] and 10 rows high. And I want Col A:A and Row 1:1 excluded I want to name the range "B2:F10" - that is where the For-Each cells are. It looks to me that your code will return the range B2:B10 Am I missing something? Thanks for your reply & assistance. Jimbo213 "Gary Keramidas" wrote: one way Dim ws As Worksheet Dim lastrow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For Each Cell In ws.Range("B2:B" & lastrow) If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
Ok I'm not communicating this well because it looks like you've hardcoded column F: In addition to LastUsedRow I need LastUsedColumn to get the LastPossibleCell. Sometimes the data is six columns wide [hense col F] sometimes it may be twenty columns wide [column S]. Sometimes ten rows deep, sometimes 508 rows deep. I need to NAME the range from B2 to the last Cell(row:column) I've written code to find the last row number. I've written code to find the last column, but instead of a letter "F" I get the number 6. Or in another case I get 20 instead of the column letter "S". The column number 6 [or 20] doesn't help me select the cell that would be at F10 [or S10] because I can't select a range using the column#6 ... now if I knew how to translate from A1 notation to R1C1 notation then the range could be NameThisRange (R2C2:LastRow&LastColumn) or something but I have no clue how to make that work either Thanks for your reply & assistance. and please keep trying. Jimbo213 "Gary Keramidas" wrote: didn't realize you wanted more than 1 column. you can use a formula from rick to help out. it will find the column with the data in the highest numbered row check it out and see if it helps. Dim ws As Worksheet Dim LastUsedRow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") LastUsedRow = ws.Columns("B:F").Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row For Each Cell In ws.Range("B2:F" & LastUsedRow) If Not IsEmpty(Cell) Then ' code for each cell here End If Debug.Print Cell.Address Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Gary: Your code (Rows.Count, "B").End(xlUp).Row and ("B2:B" & lastrow) confuses me. Let's say the spreadsheet is 6 columns wide [A to F] and 10 rows high. And I want Col A:A and Row 1:1 excluded I want to name the range "B2:F10" - that is where the For-Each cells are. It looks to me that your code will return the range B2:B10 Am I missing something? Thanks for your reply & assistance. Jimbo213 "Gary Keramidas" wrote: one way Dim ws As Worksheet Dim lastrow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For Each Cell In ws.Range("B2:B" & lastrow) If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
you're right.
try this and watch for wordwrap. the addresses included should be displayed in the immediate window Sub test() Dim ws As Worksheet Dim LastUsedRow As Long Dim LastUsedCol As Long Dim Cell As Range Set ws = Worksheets("Sheet1") LastUsedRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row LastUsedCol = ws.Cells.Find(What:="*", SearchOrder:=xlColumns, _ SearchDirection:=xlPrevious).Column For Each Cell In ws.Range(Cells(2, "B").Address & ":" & Cells(LastUsedRow, _ LastUsedCol).Address) If Not IsEmpty(Cell) Then ' code for each cell here End If Debug.Print Cell.Address Next Cell End Sub -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Ok I'm not communicating this well because it looks like you've hardcoded column F: In addition to LastUsedRow I need LastUsedColumn to get the LastPossibleCell. Sometimes the data is six columns wide [hense col F] sometimes it may be twenty columns wide [column S]. Sometimes ten rows deep, sometimes 508 rows deep. I need to NAME the range from B2 to the last Cell(row:column) I've written code to find the last row number. I've written code to find the last column, but instead of a letter "F" I get the number 6. Or in another case I get 20 instead of the column letter "S". The column number 6 [or 20] doesn't help me select the cell that would be at F10 [or S10] because I can't select a range using the column#6 ... now if I knew how to translate from A1 notation to R1C1 notation then the range could be NameThisRange (R2C2:LastRow&LastColumn) or something but I have no clue how to make that work either Thanks for your reply & assistance. and please keep trying. Jimbo213 "Gary Keramidas" wrote: didn't realize you wanted more than 1 column. you can use a formula from rick to help out. it will find the column with the data in the highest numbered row check it out and see if it helps. Dim ws As Worksheet Dim LastUsedRow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") LastUsedRow = ws.Columns("B:F").Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row For Each Cell In ws.Range("B2:F" & LastUsedRow) If Not IsEmpty(Cell) Then ' code for each cell here End If Debug.Print Cell.Address Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Gary: Your code (Rows.Count, "B").End(xlUp).Row and ("B2:B" & lastrow) confuses me. Let's say the spreadsheet is 6 columns wide [A to F] and 10 rows high. And I want Col A:A and Row 1:1 excluded I want to name the range "B2:F10" - that is where the For-Each cells are. It looks to me that your code will return the range B2:B10 Am I missing something? Thanks for your reply & assistance. Jimbo213 "Gary Keramidas" wrote: one way Dim ws As Worksheet Dim lastrow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For Each Cell In ws.Range("B2:B" & lastrow) If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
Ahhhhhh - very clever
Cells(LastUsedRow, LastUsedCol).Address can't wait to try this tomorrow at work & let you know after all the work you've put into helping me. 2 Rookie Questions, if I may ... 1) Will this work if the end-most cell is blank ex: if the worksheet is A-F x 10 rows, if F10 is blank? 2) why'd you use Dim LastUsedRow As Long instead of As Integer? I'm reading VBA-for-Dummies book and it looks like either would be OK. Thanks, Gary, for your reply & assistance. Jimbo213 "Gary Keramidas" wrote: you're right. try this and watch for wordwrap. the addresses included should be displayed in the immediate window Sub test() Dim ws As Worksheet Dim LastUsedRow As Long Dim LastUsedCol As Long Dim Cell As Range Set ws = Worksheets("Sheet1") LastUsedRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row LastUsedCol = ws.Cells.Find(What:="*", SearchOrder:=xlColumns, _ SearchDirection:=xlPrevious).Column For Each Cell In ws.Range(Cells(2, "B").Address & ":" & Cells(LastUsedRow, _ LastUsedCol).Address) If Not IsEmpty(Cell) Then ' code for each cell here End If Debug.Print Cell.Address Next Cell End Sub -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Ok I'm not communicating this well because it looks like you've hardcoded column F: In addition to LastUsedRow I need LastUsedColumn to get the LastPossibleCell. Sometimes the data is six columns wide [hense col F] sometimes it may be twenty columns wide [column S]. Sometimes ten rows deep, sometimes 508 rows deep. I need to NAME the range from B2 to the last Cell(row:column) I've written code to find the last row number. I've written code to find the last column, but instead of a letter "F" I get the number 6. Or in another case I get 20 instead of the column letter "S". The column number 6 [or 20] doesn't help me select the cell that would be at F10 [or S10] because I can't select a range using the column#6 ... now if I knew how to translate from A1 notation to R1C1 notation then the range could be NameThisRange (R2C2:LastRow&LastColumn) or something but I have no clue how to make that work either Thanks for your reply & assistance. and please keep trying. Jimbo213 "Gary Keramidas" wrote: didn't realize you wanted more than 1 column. you can use a formula from rick to help out. it will find the column with the data in the highest numbered row check it out and see if it helps. Dim ws As Worksheet Dim LastUsedRow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") LastUsedRow = ws.Columns("B:F").Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row For Each Cell In ws.Range("B2:F" & LastUsedRow) If Not IsEmpty(Cell) Then ' code for each cell here End If Debug.Print Cell.Address Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Gary: Your code (Rows.Count, "B").End(xlUp).Row and ("B2:B" & lastrow) confuses me. Let's say the spreadsheet is 6 columns wide [A to F] and 10 rows high. And I want Col A:A and Row 1:1 excluded I want to name the range "B2:F10" - that is where the For-Each cells are. It looks to me that your code will return the range B2:B10 Am I missing something? Thanks for your reply & assistance. Jimbo213 "Gary Keramidas" wrote: one way Dim ws As Worksheet Dim lastrow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For Each Cell In ws.Range("B2:B" & lastrow) If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign a range-name from B2 to {end of data}
if you have data in cells D9,G10, J4, N22, and R1, for example. this will
test all of the cells in this range: $B$2:$R$22 because the last used column is 18, "R" and the last used row (highest row number) is 22 in column "N" remember, watch for wordwrap. the "for" line will pop an error if you just copy the code. look for the "_" characters, that's where i split it i used long because long data type has a range from -2,147,483,648 to 2,147,483,647, while integer data type has a range -32,768 to 32,767. even excel 2003 has more rows than an integer data type can support, 65K, and excel 2007/2010 has a million. while you may never get to that limit, why use integer if it could cause an error sometime in the future. -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Ahhhhhh - very clever Cells(LastUsedRow, LastUsedCol).Address can't wait to try this tomorrow at work & let you know after all the work you've put into helping me. 2 Rookie Questions, if I may ... 1) Will this work if the end-most cell is blank ex: if the worksheet is A-F x 10 rows, if F10 is blank? 2) why'd you use Dim LastUsedRow As Long instead of As Integer? I'm reading VBA-for-Dummies book and it looks like either would be OK. Thanks, Gary, for your reply & assistance. Jimbo213 "Gary Keramidas" wrote: you're right. try this and watch for wordwrap. the addresses included should be displayed in the immediate window Sub test() Dim ws As Worksheet Dim LastUsedRow As Long Dim LastUsedCol As Long Dim Cell As Range Set ws = Worksheets("Sheet1") LastUsedRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row LastUsedCol = ws.Cells.Find(What:="*", SearchOrder:=xlColumns, _ SearchDirection:=xlPrevious).Column For Each Cell In ws.Range(Cells(2, "B").Address & ":" & Cells(LastUsedRow, _ LastUsedCol).Address) If Not IsEmpty(Cell) Then ' code for each cell here End If Debug.Print Cell.Address Next Cell End Sub -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Ok I'm not communicating this well because it looks like you've hardcoded column F: In addition to LastUsedRow I need LastUsedColumn to get the LastPossibleCell. Sometimes the data is six columns wide [hense col F] sometimes it may be twenty columns wide [column S]. Sometimes ten rows deep, sometimes 508 rows deep. I need to NAME the range from B2 to the last Cell(row:column) I've written code to find the last row number. I've written code to find the last column, but instead of a letter "F" I get the number 6. Or in another case I get 20 instead of the column letter "S". The column number 6 [or 20] doesn't help me select the cell that would be at F10 [or S10] because I can't select a range using the column#6 ... now if I knew how to translate from A1 notation to R1C1 notation then the range could be NameThisRange (R2C2:LastRow&LastColumn) or something but I have no clue how to make that work either Thanks for your reply & assistance. and please keep trying. Jimbo213 "Gary Keramidas" wrote: didn't realize you wanted more than 1 column. you can use a formula from rick to help out. it will find the column with the data in the highest numbered row check it out and see if it helps. Dim ws As Worksheet Dim LastUsedRow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") LastUsedRow = ws.Columns("B:F").Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row For Each Cell In ws.Range("B2:F" & LastUsedRow) If Not IsEmpty(Cell) Then ' code for each cell here End If Debug.Print Cell.Address Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... Gary: Your code (Rows.Count, "B").End(xlUp).Row and ("B2:B" & lastrow) confuses me. Let's say the spreadsheet is 6 columns wide [A to F] and 10 rows high. And I want Col A:A and Row 1:1 excluded I want to name the range "B2:F10" - that is where the For-Each cells are. It looks to me that your code will return the range B2:B10 Am I missing something? Thanks for your reply & assistance. Jimbo213 "Gary Keramidas" wrote: one way Dim ws As Worksheet Dim lastrow As Long Dim Cell As Range Set ws = Worksheets("Sheet1") lastrow = ws.Cells(Rows.Count, "B").End(xlUp).Row For Each Cell In ws.Range("B2:B" & lastrow) If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Gary Keramidas Excel 2003 "Jimbo213" wrote in message ... I've searched all of the discussion sub-groups and can't find a solution for this. I want to Assign a range-name from B2 to {end of data} so I can perform a for-each-cell next-cell loop. My spreadsheet is exported from another application that I have no control over [isn't that always the case !!] Each export has contiguous [no spaces] data in up to 1000 rows and up to 15 columns. Each run is quite different. Some data cells [B2:end] may be blank. Even the last cell in the bottom-right corner may be blank. GoTo Special Current Region seens to catch all the data but also includes column A and row 1 ... For example with data in A1 to E10 I want to name the range B2:E10 NOTE: Not from A1 because I don't want the range to include Row 1 or Column A Also, FYI, after the last data there are 4 blank lines and then several summary rows in [for example: A15 to A20] CTRL + SHIFT + END catches them. How do I range-name the SELECTION from B2 to the end-of-data [E10 in above] so this code works to cycle through all the data cells. Dim Cell As Range For Each Cell In SELECTION If Not IsEmpty(Cell) Then ' code for each cell here End If Next Cell -- Thanks for your reply & assistance. Jimbo213 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I assign a letter value to a range of data? | Excel Discussion (Misc queries) | |||
assign value to range | Excel Programming | |||
Assign number to every value in a data range??? | Excel Worksheet Functions | |||
Assign a Name to a Range in VBA | Excel Programming | |||
assign a value to a range | Excel Discussion (Misc queries) |