Moving down to the next Row that has data in it

[HunterN]

Hi,

I have the following columns A, B and C.

A B C
123 b x

455 b x
789 b x
672 b x

143 b x

---------------------------
Say something is in Col A (but not on every row). But in some instances
there is data in Col A one row after the next. How do I move from one
row to the next ( in Col A ) and not jump over any rows with data.

This is the code I currently have:

Do While LoopRw <= final_Row
Call ReCalculate_Location

If LoopRw < final_Row Then
Selection.End(xlDown).Select
LoopRw = ActiveCell.Row
Else
LoopRw = LoopRw + 1
End If
Loop

My problem is the Selection.End(xlDown).Select line. When the above code runs it skips over the row with 789 in the column. Watching it in debug it jumps over one (or more) and go to the bottom row of a group. So the Call ReCalculate_Location gets skipped for some rows.

I hope someone can reduplicate this problem and help me out here!
Thanks, Nancy

 

[njimack]

Nancy,

What are you trying to accomplish with your code? It's rarely necessary to select cells to manipulate them.

 

[HunterN]

My real spreadsheet has more rows in it than I posted.

Between each row there could be numerous rows of data in columns B thru I. For every variable in Column A, - Col C, Col G and Col H could have numerous rows. What I do is find the variable in Col A and establish that row number and then I find the row the next variable starts in Col A. I then need to move through these rows and move the data to a new sheet, but in this case the data is going to be inserted below each item, all in Col A. What I am creating is a new document and saving as text so that another computer system can read it.

I know I should not be selecting. But I can never figure it out correctly any other way.

Hope this makes sense! Nancy

 

[Norie]

Nancy

So what you want to do is create a new sheet for every 'block' of data?

If so why not get the last row like this.

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

Then loop through all the rows from 1 to LastRow.

Sub test()
Dim wsData
Dim ws As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim I As Long

    Set wsData = ActiveSheet
    LastRow = wsData.Range("A" & Rows.Count).End(xlUp).Row
    
    For I = 1 To LastRow
        Set rng = wsData.Range("A" & I)
        If rng.Offset(1).Value = "" Then
            Set ws = Worksheets.Add
            rng.CurrentRegion.Copy ws.Range("A1")
        End If
    Next I
End Sub