Macro for inserting rows 1

[nikniknik]

Hi All,

Sorry for such a question, but I'm still learning about VBA. I know how to create a macro for inserting rows, but I need to alter this code to help my problem. I don't know where to start.

Here's my problem:

I have a huge data file where column A is the unique identifier. For example,

Column A
A
A
A
A
A
A
A
B
B
B
B
B
B
B
B
C
C
C
C
C
C

The data starts on (1,1). I need to create breaks in between the blocks of data where B starts on row 100, C starts on row 200, etc.

Any suggestions?

 

[SkipVought]

nikniknik,

Bad idea to insert empty rows or columns in your data table. BTW, in your posted data example Col A is NOT a unique identifier.

What are you trying to do? Could you FORMAT the "break row" using a double row height?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nikniknik]

Hi Skip,

It may be a bad idea to insert empty rows, but that's what I have to do. I need the next data set to start on row 100 and the next on row 200 and then next on row 300, etc.

nik

 

[SkipVought]

...and the reason (business case) is???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nikniknik]

The reason is that the file needs to be in this format because once I manually insert these rows (which take a fair amount of time)I then copy the file into a different system, which performs an auto check on some prices. The system that I'm copying the file into requires it to be in this format otherwise the check will be comparing the wrong numbers.

nik

 

[nikniknik]

I came up with this and I think it's work, but my syntax is wrong. Can someone please help?

Sub DoStuff()
Dim allRows As Range
Set allRows = Cells(Rows.Counter, "L").End(xlUp)
Set i = 0
Do While (i <> Rows.Counter)
if (allRows.Row(i).Cell(x) <> allRows.Row(i + 1).Cell (x+1))

allRows.EntireRow.InsertAt (i + (100 - (i Mod 100)))
End If
End If
End Sub

 

[SkipVought]

Sub DoStuff()
    Dim allRows As Range
    Set allRows = Cells(Rows.Counter, "L").End(xlUp)
    Set i = 0
    Do While (i <> Rows.Counter)
       If (allRows.Row(i).Cell(x) <> allRows.Row(i + 1).Cell(x + 1)) Then
         
          allRows.EntireRow.InsertAt (i + (100 - (i Mod 100)))
       End If
    End If
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nikniknik]

Thanks Skip!!!

 

[nikniknik]

Hi Skip,

When I try to run the macro, the complier states "Object Required" at the line: Set i=0. Suggestions?

nik

 

[SkipVought]

Sub DoStuff()
    Dim allRows As Range, RowCount As Long, x As Long, i As Long
    RowCount = ActiveSheet.UsedRange.Rows.Count
    Set allRows = Cells(RowCount, "L").End(xlUp)
    i = 1
    x = 0
    With allRows
        Do While (i <> RowCount)
           If (.Rows(i).Cell(x) <> .Rows(i + 1).Cell(x + 1)) Then
             
              .Rows(i + (100 - (i Mod 100))).EntireRow.Insert shift:=xlDown
           End If
        
        'you have to do something to incriment i & x
        'x = i + 1 ???
        'i = i + ???
        'I really don't know what your logic is
        Loop
    End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

I'm sorry, I didn't look at this carefully the first time I sent it back

Sub DoStuff()
    Dim allRows As Range, RowCount As Long, x As Long, i As Long
    RowCount = ActiveSheet.UsedRange.Rows.Count
    Set allRows = Cells(RowCount, "L").End(xlUp)
    i = 1
    x = 1
    With allRows
        Do While (i <> RowCount)
            If (.Cells(x, 1) <> .Cells(i, 1)) Then
             
              Range(.Cells(i, 1), .Cells(i + (100 - (i Mod 100)), 1)).EntireRow.Insert shift:=xlDown
              i = i + (100 - (i Mod 100))
              RowCount = RowCount + (100 - (i Mod 100))
              x = i + 1
            End If
        
        'you have to do something to incriment i & x
        i = i + 1
        'I really don't know what your logic is
        Loop
    End With
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nikniknik]

Hi Skip,

I just mananged to find time to play with this now. I altered the code very slightly (all of which shouldn't make a difference). I'm getting an error in the if stmt...says application-defined error or object-defined error. Thoughts?


Sub DoStuff()
Dim allRows As Range, RowCount As Long, x As Long, i As Long
RowCount = ActiveSheet.UsedRange.Rows.Count
Set allRows = Cells(RowCount, "L").End(xlUp)
i = 1
With allRows
Do While (i <> RowCount)
If (.Cells(i + 1, 1) <> .Cells(i, 1)) Then

Range(.Cells(i, 1), .Cells(i + (100 - (i Mod 100)), 1)).EntireRow.Insert shift:=xlDown
i = i + (100 - (i Mod 100))
RowCount = RowCount + (100 - (i Mod 100))
End If

i = i + 1

Loop
End With
End Sub

 

[SkipVought]

If (.Cells(i + 1, 1).value <> .Cells(i, 1).value) Then

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nikniknik]

Sorry, but here's another stupid question.

Does .Value return an integer value because the data in cell(i, 1) are string?

The complier is not liking the syntax of the line of code you just gave me.

nik

 

[SkipVought]

nik,

actually, Cells(row, col) is a range object.

Cells(row, col).Value os the value, text or numeric in the range object.

I can't really tell you why you are getting an error. I copied the code and it runs without error WITH or WITHOUT the .Value.

I don't really like the Set Statement here. It does not really add anything to the process. Basically, it locates the first row of data in COLUMN L (assuming a contiguous range of data). And that leads me to ask, what allRows actually has. When the code stops, hit Debug and add a watch on allrows. Tell me what you observe in the watch window.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[nikniknik]

I don't think I need the set, so deleted it...now I have

Sub DoStuff()
Dim RowCount As Long, x As Long, i As Long
RowCount = ActiveSheet.UsedRange.Rows.Count

i = 1

Do While (i <> RowCount)
If (Cells(i + 1, 1).Value <> Cells(i, 1).Value)Then

Range(Cells(i, 1), Cells(i + (100 - (i Mod 100)), 1)).EntireRow.Insert
i = i + (100 - (i Mod 100))
RowCount = RowCount + (100 - (i Mod 100))
End If

i = i + 1

Loop

End Sub

When I initiate the macro, it separates the first block of data from the second block, but runs all the data after the first block right off the spread sheet.

Thanks so much for all your help Skip!

 

[SkipVought]

Try running your loop from the bottom up rather than top down

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884