(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

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

with minor modifications, this worked out well!

'====== 01.23.08 new code for addresses
Dim lColumn
Dim lRow2
Dim tRange

lColumn = Range("C5").End(xlToRight).Column
lRow2 = Range("C5").End(xlDown).Row
If lRow2 = "1048576" Then
lRow2 = Range("C5").End(xlUp).Row
lRow2 = lRow + 4
End If
tRange = Range("C5", Cells(lRow2, lColumn)).Address
'MsgBox "lColumn " & lColumn 'for testing
'MsgBox "lRow2 " & lRow2 'for testing
'MsgBox "tRange " & tRange 'for testing
'End FindCellRange

Set rRngCol = Range(tRange)
'======
As for empty cells, I filled them with "n/a", solves that problem.

Thanks Per!
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"Per Jessen" wrote:

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