easiest way to find next non blank row
 

given sheet with data on x rows
then x rows blank
then x rows data
then x rows blank
etc

is there an easy way to jump from data group to data group?
or just increment a counter and check each row(some cell in row) for
..value = "" ?

thanks
mark

easiest way to find next non blank row
 

After serious thinking mp wrote :
given sheet with data on x rows
then x rows blank
then x rows data
then x rows blank
etc

is there an easy way to jump from data group to data group?
or just increment a counter and check each row(some cell in row) for .value =
"" ?

thanks
mark

Check out using the End() method with Range/Cells/Selection/ActiveCell
objects, and compare how it works with using the keyboard combo of
holding down Ctrl while pressing one of the Arrow keys.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

easiest way to find next non blank row
 

On 4/16/2012 8:02 PM, GS wrote:
Check out using the End() method with Range/Cells/Selection/ActiveCell
objects, and compare how it works with using the keyboard combo of
holding down Ctrl while pressing one of the Arrow keys.

Thanks Gs
I couldn't figure out how to use the .End property
This is an abortion but it works to find my "groups of data" for each
"symbol"

Don't know if you can figure out from this code how .End would simplify
the basic idea?

the data is divided by blank rows into groups
each group refers to a "symbol" or name

this code just prints the "symbol" and first and last row of data for
that group to confirm that i am dividing up the data groups correctly

Function GetNextNonBlankRow() As Long
Dim oWks As Worksheet
Set oWks = ActiveSheet
Dim RowIndex As Long, ColIndex As Long
RowIndex = 1
ColIndex = 3
Dim RowLimit As Long
RowLimit = 50
Dim ThisSymbol As String
Dim LastSymbol As String

Dim oRng As Range
With oWks

'couldn't figure out how to use the End property
' For RowIndex = 1 To RowLimit
' Set oRng = .Cells(RowIndex, ColIndex)
' If oRng.End(xlDown).Value < "" Then
' Debug.Print oRng.End(xlDown).Value, RowIndex
' End If
' Next

For RowIndex = 14 To RowLimit
If .Cells(RowIndex, ColIndex).Value = "" Then

If LastSymbol < "" Then Debug.Print "Last row ",
RowIndex - 1
LastSymbol = ""
Else
ThisSymbol = .Cells(RowIndex, ColIndex).Value
If LastSymbol = ThisSymbol Then

Else
Debug.Print "First row ", RowIndex
Debug.Print ThisSymbol
LastSymbol = ThisSymbol
End If
End If
Next
End With
Debug.Print "done"

End Function

easiest way to find next non blank row
 

I suggest a different approach...

1. I need to know which row/column the data starts.

2. I need to find the last row containing data.

3. I need to get the first block of data to the last block of data.

4. I need to process each block in turn.

5. I need to end the procedure on the last row of the last block.


Example code for a set of data starting in Row1/Col1:

Sub GetDataBlocks()
Dim lLastRow&, lLastCol&, lStartRow&, lStartCol&, r1&, r2& 'as long
Dim sBlocks$ 'as string
Dim v As Variant

'The first/last row of data
lStartRow = 1: lStartCol = 1 'edit to suit
lLastRow = Cells(Rows.Count, lStartCol).End(xlUp).Row

'Get the blocks of data
'The 1st block
r1 = lStartRow
r2 = Cells(r1, lStartCol).End(xlDown).Row
sBlocks = r1 & ":" & r2
'The remaining blocks
Do Until r2 = lLastRow
r1 = Cells(r2, lStartCol).End(xlDown).Row
r2 = Cells(r1, lStartCol).End(xlDown).Row
sBlocks = sBlocks & "," & r1 & ":" & r2
Loop

'Process the blocks
For Each v In Split(sBlocks, ",")
Debug.Print Rows(v).Address
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

easiest way to find next non blank row
 

On 4/16/2012 10:38 PM, GS wrote:
I suggest a different approach...

1. I need to know which row/column the data starts.

2. I need to find the last row containing data.

3. I need to get the first block of data to the last block of data.

4. I need to process each block in turn.

5. I need to end the procedure on the last row of the last block.


Example code for a set of data starting in Row1/Col1:

Sub GetDataBlocks()
Dim lLastRow&, lLastCol&, lStartRow&, lStartCol&, r1&, r2& 'as long
Dim sBlocks$ 'as string
Dim v As Variant

'The first/last row of data
lStartRow = 1: lStartCol = 1 'edit to suit
lLastRow = Cells(Rows.Count, lStartCol).End(xlUp).Row

'Get the blocks of data
'The 1st block
r1 = lStartRow
r2 = Cells(r1, lStartCol).End(xlDown).Row
sBlocks = r1 & ":" & r2
'The remaining blocks
Do Until r2 = lLastRow
r1 = Cells(r2, lStartCol).End(xlDown).Row
r2 = Cells(r1, lStartCol).End(xlDown).Row
sBlocks = sBlocks & "," & r1 & ":" & r2
Loop

'Process the blocks
For Each v In Split(sBlocks, ",")
Debug.Print Rows(v).Address
Next 'v
End Sub

Garry,
That is wayyyy cool. Thank you so much.
Mark

easiest way to find next non blank row
 

mp has brought this to us :
On 4/16/2012 10:38 PM, GS wrote:
I suggest a different approach...

1. I need to know which row/column the data starts.

2. I need to find the last row containing data.

3. I need to get the first block of data to the last block of data.

4. I need to process each block in turn.

5. I need to end the procedure on the last row of the last block.


Example code for a set of data starting in Row1/Col1:

Sub GetDataBlocks()
Dim lLastRow&, lLastCol&, lStartRow&, lStartCol&, r1&, r2& 'as long
Dim sBlocks$ 'as string
Dim v As Variant

'The first/last row of data
lStartRow = 1: lStartCol = 1 'edit to suit
lLastRow = Cells(Rows.Count, lStartCol).End(xlUp).Row

'Get the blocks of data
'The 1st block
r1 = lStartRow
r2 = Cells(r1, lStartCol).End(xlDown).Row
sBlocks = r1 & ":" & r2
'The remaining blocks
Do Until r2 = lLastRow
r1 = Cells(r2, lStartCol).End(xlDown).Row
r2 = Cells(r1, lStartCol).End(xlDown).Row
sBlocks = sBlocks & "," & r1 & ":" & r2
Loop

'Process the blocks
For Each v In Split(sBlocks, ",")
Debug.Print Rows(v).Address
Next 'v
End Sub

Garry,
That is wayyyy cool. Thank you so much.
Mark

Glad to help...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion