CountA different for page than when using in VBA

[ungoliath]

I'm trying to have a sorting macro that finds the range size by using the counta function. However when I run this code I get a value for counta of 1 but a value of 4 when I use it directly within excel.

Sub Macro1()
Dim counterx As Integer
Dim countery As Integer

Sheets("POC Information").Select
counterx = WorksheetFunction.CountA("i3:xfd3")
countery = WorksheetFunction.CountA("C:C") - 1


With ActiveWorkbook.Worksheets("POC Information").Sort
.SetRange Range("B3:L" & countery)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub


Can anyone tell me what's going on?

 

[VoG]

Try

counterx = WorksheetFunction.CountA(Range("i3:xfd3"))
countery = WorksheetFunction.CountA(Columns("C")) - 1

 

[ungoliath]

Thanks, one more question though.

How would I do that using R1C1 notation?

 

[ungoliath]

I've got this but it's not working

Sub Macro1()
Dim counterx As Integer
Dim countery As Integer

Sheets("POC Information").Select
counterx = Application.WorksheetFunction.CountA(Range("i3:xfd3"))
countery = Application.WorksheetFunction.CountA(Columns("C:C")) - 1


With ActiveWorkbook.Worksheets("POC Information").Sort
.SetRange Range("R[3]C[2]:R[" & (countery + 3) & "]C[" & (counterx + 8) & "]")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[VoG]

Possibly

.SetRange Range("R3C2:R[" & countery + 3 & "]C[" & counterx + 8 & "]")

 

[ungoliath]

Thanks,

I tried that first but still wound up w/ errors. I did however read about the Cells command and was able to get it to work w/ this line of code.

.SetRange Range(Cells(3, 2), Cells(countery + 5, counterx + 8))