Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I insert row after certin data in cel 1

Status
Not open for further replies.

7263

IS-IT--Management
May 13, 2004
6
US
I have a spreadsheat and would like to insert row after specific data
 
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
 
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
 
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
 
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
 
Code:
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
 
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
 
Code:
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
 
one more question how would I put in data in the inserted line only in column A

Thank You
 
Code:
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
 
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.
 

eshie,

Please post your question in a NEW THREAD.

Skip,

[glasses] [red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top