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

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" <(E-Mail Removed)> wrote in message
news:6DE006FB-5B92-4F27-9043-(E-Mail Removed)...
>
> 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

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" <(E-Mail Removed)> wrote in message
news:6DE006FB-5B92-4F27-9043-(E-Mail Removed)...
>
> 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

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" <(E-Mail Removed)> wrote in message
> news:6DE006FB-5B92-4F27-9043-(E-Mail Removed)...
> >
> > 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
>
>

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" <(E-Mail Removed)> wrote in message
news:3F5B9D4D-310A-41F8-BF8D-(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
>> news:6DE006FB-5B92-4F27-9043-(E-Mail Removed)...
>> >
>> > 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
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:3F5B9D4D-310A-41F8-BF8D-(E-Mail Removed)...
> >
> > 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" <(E-Mail Removed)> wrote in message
> >> news:6DE006FB-5B92-4F27-9043-(E-Mail Removed)...
> >> >
> >> > 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
> >>
> >>
>
>

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" <(E-Mail Removed)> wrote in message
news:30D695AF-3877-4004-9B29-(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
>> news:3F5B9D4D-310A-41F8-BF8D-(E-Mail Removed)...
>> >
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:6DE006FB-5B92-4F27-9043-(E-Mail Removed)...
>> >> >
>> >> > 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
>> >>
>> >>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:30D695AF-3877-4004-9B29-(E-Mail Removed)...
> >
> > 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" <(E-Mail Removed)> wrote in message
> >> news:3F5B9D4D-310A-41F8-BF8D-(E-Mail Removed)...
> >> >
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:6DE006FB-5B92-4F27-9043-(E-Mail Removed)...
> >> >> >
> >> >> > 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
> >> >>
> >> >>
> >>
> >>
>
>

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" <(E-Mail Removed)> wrote in message
news:3FC194DE-AF2D-421A-AF1B-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:30D695AF-3877-4004-9B29-(E-Mail Removed)...
>> >
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:3F5B9D4D-310A-41F8-BF8D-(E-Mail Removed)...
>> >> >
>> >> > 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" <(E-Mail Removed)> wrote in message
>> >> >> news:6DE006FB-5B92-4F27-9043-(E-Mail Removed)...
>> >> >> >
>> >> >> > 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
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>