View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Gary Keramidas Gary Keramidas is offline
external usenet poster
 
Posts: 2,494
Default 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