How to create "Array" that looks at column values?

[dpnab]

I currently use the following VBA formula to print out a separate sheet based on each "ABC" value in the "array".

Sub PrintAll()
Dim i As Integer
Dim VList As Variant

VList = Array("ABC123","ABC456","ABC789")

For i = LBound(VList) To UBound(VList)
Range("B1") = VList(i)
ActiveSheet.PrintOut
Next
End Sub

I have a list of items in Sheet "Products" and in Column A starting from A2, I have all these product numbers. Instead of manually entering them into the macro (ABC123, ABC456, ABC789, etc etc), I want it to just use all the values that in in Column A.

This will change on a daily basis, so I'd like it to search in Column A and stop populating when a cell is blank.

What can I add to do this?

Thanks!

 

[Fluff]

Ok, how about

Sub PrintAll()
Dim i As Integer
Dim VList As Variant

With Sheets("main")
   VList = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
End With
If IsArray(VList) Then
   For i = LBound(VList) To UBound(VList)
   Range("B1") = VList(i, 1)
   ActiveSheet.PrintOut
   Next
Else
   Range("B1") = VList
   ActiveSheet.PrintOut
End If
End Sub

 

[dpnab]

Ok, how about

Sub PrintAll()
Dim i As Integer
Dim VList As Variant

With Sheets("main")
   VList = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
End With
If IsArray(VList) Then
   For i = LBound(VList) To UBound(VList)
   Range("B1") = VList(i, 1)
   ActiveSheet.PrintOut
   Next
Else
   Range("B1") = VList
   ActiveSheet.PrintOut
End If
End Sub

That did it, thanks!

 

[Fluff]

You're welcome & thanks for the feedback.