Gary Keramidas
7/29/2009 5:04:00 AM
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" <Jimbo213@discussions.microsoft.com> wrote in message
news:3FC194DE-AF2D-421A-AF1B-5CB2FC2F2D0C@microsoft.com...
> 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" <Jimbo213@discussions.microsoft.com> wrote in message
>> news:30D695AF-3877-4004-9B29-C2717C282878@microsoft.com...
>> >
>> > 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" <Jimbo213@discussions.microsoft.com> wrote in message
>> >> news:3F5B9D4D-310A-41F8-BF8D-2058DE39CF3A@microsoft.com...
>> >> >
>> >> > 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" <Jimbo213@discussions.microsoft.com> wrote in message
>> >> >> news:6DE006FB-5B92-4F27-9043-BB457C83B63B@microsoft.com...
>> >> >> >
>> >> >> > 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
>> >> >>
>> >> >>
>> >>
>> >>
>>
>>