Assign a range-name from B2 to {end of data}

[Jimbo213]

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

 

[Gary Keramidas]

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

 

[AltaEgo]

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

 

[Jimbo213]

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

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

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

 

[Gary Keramidas]

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

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

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

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

 

[Jimbo213]

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

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

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

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



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

 

[Gary Keramidas]

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

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

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

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


:

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



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

 

[Jimbo213]

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

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

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

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



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


:

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



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

 

[Gary Keramidas]

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

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

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

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


:

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



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


:

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



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