How to identify Last Row?

[Cummins]

Submitting again. Different issue. The spreadsheet I am pulling from is locked except for the first column so that the user doesn't screw anything up. I don't like the text box entry method which is why I am doing it this way. I have used lastrow in other applications and it works. The code didn't have an issue with lastrow until I fixed the issue I was having with lastrow. Is there a property that would allow me to only look at column A to avoid the Locked/Protected cell issue?




Sub PullManualBatchData()

lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).
'Dim F1 As Object
Dim Ary1(3, 1) As String

Set F1 = ActiveWorkbook.ActiveSheet
F1.Activate

'h = FreeFile

'Open File1 For Input As #h

g = 0

For x = 1 To lastrow
data = F1.Cells(x, 1)
If data <> "" Then Ary1(g, 1) = data And g = g + 1
Next
'Loop


'ThisWorkbook.Activate

Workbook_Open

End Sub

 

[VoG]

Try

lastrow = Range("A" & Rows.Count).End(xlUp).Row

 

[Cummins]

Awesome. Have a drink.
Any idea why data will hold a value but in the "If" statement, Ary1 never gets the value as if data was actually empty?
I.E. data will show that it has stored 666666 as a value but Ary1 stays empty?

 

[VoG]

You can't use And like that. If you want multiple statements on one line use :

g = 0

For x = 1 To lastrow
    Data = F1.Cells(x, 1).Value
    If Data <> "" Then Ary1(g, 1) = Data: g = g + 1
Next
Loop

 

[Cummins]

How can Ary1 be defined with a variable?




Sub PullManualBatchData()

Set F1 = ActiveWorkbook.ActiveSheet
F1.Activate
lastrow = F1.Range("A" & Rows.Count).End(xlUp).Row
Dim Ary1(lastrow, 1) As String
g = 0
For x = 1 To lastrow
data = Str(F1.Cells(x, 1))
If data <> "" Then Ary1(x, 1) = data: g = g + 1
Next

Workbook_Open

End Sub

 

[VoG]

Perhaps like this

Sub PullManualBatchData()
Dim F1 As Worksheet, LastRow As Long, Ary1() As Variant
Set F1 = ActiveWorkbook.ActiveSheet
F1.Activate
LastRow = F1.Range("A" & Rows.Count).End(xlUp).Row
ReDim Ary1(LastRow, 1)
g = 0
For x = 1 To LastRow
    Data = Str(F1.Cells(x, 1))
    If Data <> "" Then Ary1(x, 1) = Data: g = g + 1
Next

'Workbook_Open huh?

End Sub