On 23 Jan., 01:02, Rick S. wrote:
(Looks at a box.)
I'm stuck.

The code below reads the cell.address and I strip the "$" signs. *Then I
test for character length and create a range, IE, "A1" or "Z99" etc. *I
recently found myself using double alpha characters on one sheet, IE, "AA6". *
I am not seeing how I can get the propper information to continue creating a
range I want.

As an example, a particular worksheet has data from A1 thru AA6, I attempt
to create a range from C5 thru AA7 with my current code but with the double
alpha address I get a range that is the opposite of my target, C6 thru A5 (6
total cells when it should be 48 total cells from C5 thru AA6).

As always, your help is appreciated!

'======
* * * * 'Begin FindCellRange=====================================
* * * * Dim x As Long, SRng As Range, Rcount As Long
* * * * Dim sRange
* * * * Dim sRow As String
* * * * Dim sRow2 As String

* * * * Set SRng = ActiveSheet.UsedRange
* * * * Rcount = SRng.Rows.Count
* * * * For x = Rcount + SRng.Row To 1 Step -1
* * * * * * If Application.CountA(SRng.Rows(x)) < 0 Then Exit For
* * * * Next x
* * * * 'MsgBox "First empty row is " & SRng.Rows(x + 1).Address(0, 0) 'for
testing value
* * * * sRow = Right(SRng.Rows(x + 1).Address(0, 0), 3)
* * * * sRow = Replace(sRow, ":", "")
* * * * If Len(sRow) = "2" Then
* * * * * * sRow2 = Left(sRow, 1)
* * * * * * 'MsgBox "Len(sRow) = ""2"" " & sRow2 'for testing
* * * * Else
'double alpha characters fail, IE: address "AA7". * 01.20.08
* * * * * * If Len(sRow) = "3" Then
* * * * * * * * sRow2 = Left(sRow, 2)
* * * * * * * * 'MsgBox "Len(sRow) = ""2"" IsNumeric " & sRow2 'for testing
* * * * * * * * Else
* * * * * * * * sRow2 = Left(sRow2, 1)
* * * * * * * * 'MsgBox "Len(sRow) = ""3"" " & sRow2 'for testing
* * * * * * End If
* * * * End If
* * * * 'End FindCellRange===================================== ==
'======
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

Hi Rick

Assuming your data always start in cell A1 and all cells in used range
is filled I would do something like this:

lColumn = Range("A1").End(xlToRight).Column
lRow = Range("A1").End(xlDown).Row
tRange = Range("C6", Cells(lRow + 1, lColumn)).Address

Regards,

Per