Help needed - check the size of a range and create a new array

[HollJett]

Hi Guys,

I'm a total newb at VBA so please excuse any possible nonsense.
I've searched the web and this site for possible solutions to no vail.

I have a data extract in excel that changes is size everyday

today the data range is A1:R5232 with a header. It's always saved to a new workbook "GRID.xls" on a single sheet "sheet1".

I'm trying to write code in a different workbook "Summary.xls" because the data is obtain separately by various users on different computers.

I managed to open the workbook on any machine so far (assuming it's save to the desktop) using code I found online:

Sub Openworkbook()
Dim objWSHShell As Object
Dim strSpecialFolderPath As String
Dim completename As String

'On Error GoTo ErrorHandler
' Create a shell object
Set objWSHShell = CreateObject("WScript.Shell")

' Find out the path to the passed special folder,
SpecialFolderPath = objWSHShell.SpecialFolders("Desktop")
completename = SpecialFolderPath & "\DGGRID.xls"
Workbooks.Open filename:=completename

' Clean up
Set objWSHShell = Nothing

ErrorHandler:

MsgBox "Error finding " & strSpecialFolder, vbCritical + vbOKOnly, "Error"

End Sub

my remaining goals:
1. check the the size of the data range in "sheet1"
2. Sort it by the values in column "R"
3. count the number of rows whose value is in colunm "R" is NOT "CMP"
4. create an array of the proper size using the count of rows meeting the critia above.
5. copy all rows that met the criteria of column "R" to the new array.


thank you for any help you may be able to provide,
Holl

 

[HollJett]

I don't think I was clear in my post-

I can't get beyond opening the workbook.

if anybody can help me code how to check the size of the range in "sheet1" so I can create an array of the correct size without wasting memory by predefining it to 10,000 or something like that.

that would be really great. thank you,

Holl

 

[John Davis]

I don't think I was clear in my post-

I can't get beyond opening the workbook.

if anybody can help me code how to check the size of the range in "sheet1" so I can create an array of the correct size without wasting memory by predefining it to 10,000 or something like that.

that would be really great. thank you,

Holl

Does this help any?

Sub HollJett()
'
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range("R2:R" & lr)

        .Sort Key1:=Range("R2"), Order1:=xlAscending, Header:=xlGuess, _
         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
         DataOption1:=xlSortNormal
        .AutoFilter Field:=1, Criteria1:="<>CMP", Operator:=xlAnd
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Workbooks("Summary.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(3)(2)
        .AutoFilter
        .AutoFilter
        
End With
        

End Sub