How do I insert row after certin data in cel 1

[7263]

I have a spreadsheat and would like to insert row after specific data

 

[SkipVought]

Hi,

I assume that you want to do this with VBA.

Turn on the Macro Recorder

Select the ROW containing the cell of interest

Right click - select Insert Row

Turn off Macro Recorder and then inspect your code. If you have problems generalizing your code, post back with your questions.



Skip,

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

 

[7263]

thanks for responding

My question is I have a spreadsheat with a few hundred lines I would Like to insert a row after each occurrance of a particular cell (and I would like to enter text in that inserted row

 

[SkipVought]

Well how do you logically define each PARTICULAR cell?

BTW, Insert is fine EXCEPT when you have formulas that reference a previous or next row.

Skip,

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

 

[7263]

for example

A
B
C
A
B
C
A
B
C

Iwould like to insert after evry cell containing C a row with data in column A

 

[SkipVought]

Sub b()
    With Range([a1], [a1].End(xlDown))
        For r = .Row To .Row + .Rows.Count - 1
          With Cells(r, "A")
            If UCase(.Value) = "C" Then
              r = r + 1
              Cells(r, "A").EntireRow.Insert shift:=xlDown
            End If
          End With
        Next
    End With
End Sub

Skip,

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

 

[7263]

Thank you so much for your help now if I can trouble you once more now I would like to insert the row before the C
what would I have to change

 

[SkipVought]

Sub b()
    With Range([a1], [a1].End(xlDown))
        For r = .Row To .Row + .Rows.Count - 1
          With Cells(r, "A")
            If UCase(.Value) = "C" Then
              Cells(r, "A").EntireRow.Insert shift:=xlDown
              r = r + 1
            End If
          End With
        Next
    End With
End Sub

Skip,

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

 

[7263]

one more question how would I put in data in the inserted line only in column A

Thank You

 

[SkipVought]

Sub b()
    With Range([a1], [a1].End(xlDown))
        r = .Row
        r1 = .Row + .Rows.Count - 1
        Do While r <= r1
          With Cells(r, "A")
            If UCase(.Value) = "C" Then
              Cells(r, "A").EntireRow.Insert shift:=xlDown
              Cells(r, "A").Value = "BB"
              r1 = r1 + 1
              r = r + 1
            End If
            r = r + 1
          End With
        Loop
    End With
End Sub

Skip,

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

 

[7263]

Thank You

 

[eshie]

Hi,
I have a spreadsheet with 50K+ rows of. I want to search through a specific column and then insert a row upon a change in value in particular column. For example:

Column
11222234A
11222234A
222333444B
222333444B
222333444B

I want to search through this particular column and insert a blank row at each change in value. Please HELP - I don't want to have to do this manually as it is due today.

 

[SkipVought]

eshie,

Please post your question in a NEW THREAD.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}