counta and sorting a selecting in VBA

[Legacy 93538]

Hi

I am trying to write a piece of code that first does a counta on how many rows in column A have text and then use that value to do a sort on columns A:AQ and then using the counta value to define the number of rows.

I hope this makes sense. Can anyone help me as i am not sure?

k = WorksheetFunction.CountA(workbook.Range("A:A"))
workbook.Sheets(1).Range ("A1:B" & k)

this is what i have so far but i dont think its correct.

Thanks

Jeskit

 

[VoG]

It would be like this

With Sheets(1)
    k = WorksheetFunction.CountA(.Range("A:A"))
    .Range("A1:AQ" & k).Sort 'sort parameters go here
End With

 

[Legacy 93538]

hi

With Sheets(1)
    k = WorksheetFunction.CountA(.Range("A:A"))
    .Range("A1:AQ" & k).Sort Sort Key1:=Worksheets(1).Columns("B"), Order1:=xlDescending
End With

This is what i have but when i wrote it and click on the next line it came up with an error saying Expected: End of statement and hightlights Key1

But i am not sure why?

 

[VoG]

You have Sort twice. Try

With Sheets(1)
    k = WorksheetFunction.CountA(.Range("A:A"))
    .Range("A1:AQ" & k).Sort Key1:=.Columns("B"), Order1:=xlDescending
End With

 

[Legacy 93538]

Thanks very much working now!!

Cheers you have been a great help!!