Empty row
 

Hi! I have a part of a code that reads names from a long list. The length of
the list changes from time to time but the boundaries are given by the range
"position". Now I read the entire range but I want it to only read the rows
the are non empty. when the first empty rows comes then i want it to stop.
Thus the problem is the the row: Nop = rngPositions.Rows.Count
or somewhere related to that but I dont know how to get it right. pls help!


Dim rngPositions As Range
Set rngPositions =
Application.ThisWorkbook.Sheets("Data").Range("pos ition")
Dim Nop As Long
Nop = rngPositions.Rows.Count
ReDim Positions(1 To Nop) As Double
Dim i As Integer
For i = 1 To Nop
Positions(i) = rngPositions.Cells(i, 1)
Next i

Empty row
 

Hi Arne

Sub pos()

Dim rngPositions As Range
Dim i As Long
Dim Nop As Long
Dim Positions

Set rngPositions = Sheets("Data").Range("position")
Nop = rngPositions(1).End(xlDown).Row - rngPositions(1).Row + 1
Set rngPositions = rngPositions.Resize(Nop)

ReDim Positions(1 To Nop) As Double
Positions = rngPositions.Value


'for matters of control (delete it if you want)
Debug.Print rngPositions.Address

For i = LBound(Positions, 1) To UBound(Positions, 1)
Debug.Print Positions(i, 1)
Next
End Sub


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

Empty row
 

Certainly not essential, but
Debug.Print Positions(i, 1)

should be

Debug.Print Positions(i)

--
Regards,
Tom Ogilvy



"Wigi" wrote:

Hi Arne

Sub pos()

Dim rngPositions As Range
Dim i As Long
Dim Nop As Long
Dim Positions

Set rngPositions = Sheets("Data").Range("position")
Nop = rngPositions(1).End(xlDown).Row - rngPositions(1).Row + 1
Set rngPositions = rngPositions.Resize(Nop)

ReDim Positions(1 To Nop) As Double
Positions = rngPositions.Value


'for matters of control (delete it if you want)
Debug.Print rngPositions.Address

For i = LBound(Positions, 1) To UBound(Positions, 1)
Debug.Print Positions(i, 1)
Next
End Sub


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music

Empty row
 

OK. Taken note of.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"Tom Ogilvy" wrote:

Certainly not essential, but
Debug.Print Positions(i, 1)

should be

Debug.Print Positions(i)

--
Regards,
Tom Ogilvy