Help with inserting lines

[vikram]

I have cells in column A where it is written "Account" in some cells.

what i want is that upon finding "Account" in cell A it inserts a lin
below it and then find another text " Account"

please help

and another macro which upon finding "Customer" in column deletes tha
entire ro

 

[Frank Kabel]

Hi
try the following macro for inserting rows
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If lcase(Cells(row_index+1, "A").Value) ="account" Then
Cells(row_index + 1, "A").EntireRow.Insert _
(xlShiftDown)
End If
Next
End Sub

To delete rows try
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If lcase(Cells(row_index, "A").Value) = "customer" then
Cells(row_index, "A").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

 

[Andy Brown]

Hi Frank, heads up.

I'll never understand that stuff/get beyond kludging like

Range("A65536").End(xlUp).Range("A2").Value = "&&&"
Range("A1").Select
Do Until ActiveCell.Value = "&&&"
If ActiveCell = "Account" Then
ActiveCell.Range("A2").EntireRow.Insert
ActiveCell.Range("A3").Select
Else
ActiveCell.Range("A2").Select
End If
Loop
Range("A65536").End(xlUp).Value = ""

However, OP said to insert a line *below* "Account ; so I think you'd need

If ... Then
Cells(row_index + 2, "A").EntireRow.Insert
End If

Rgds,
Andy

 

[Frank Kabel]

Hi
though not tested in Excel (as I'm currently re-installing Office) this
macro should insert the row below

 

[vikram]

Hi Frank,

It is not working the macro for inserting lines.
It is showing error 13 ...type mismatc

 

[Frank Kabel]

Hi
works for me without a problem. Though you have to change 'XlShiftDown'
to 'xlShiftUp'
in which line do you get this error?

 

[vikram]

If LCase(Cells(row_index + 1, "A").Value) = "Account" Then


in this row

 

[Frank Kabel]

Hi
what kind of values do you have in this row. Do you have error values
in this range?

 

[vikram]

2 Wire Inc
99129
Account
3 Vets Inc
186743
Account
3Com Corp
95991
Account
3G Wireless LLC
198734
Account
3M Co
99130
Account
3PARdata Inc
99132
Account
454 Corporation
136213
Account
A & M Electronics
125149
Account

this is a part of column

 

[Andy Brown]

2 Wire Inc

, etc.

Did you try the brute force version? Worked for me.

<vbg>,
Andy

 

[Frank Kabel]

Hi
the macro works for me on this data (just make sure you use the line
If LCase(Cells(row_index + 1, "A").Value) = "account" Then

instead of
If LCase(Cells(row_index + 1, "A").Value) = "Account" Then

 

[vikram]

Hey frank!


i was checking ur macro.......the row index part picks up date from ro
number 22000 ,,,, i have 35000 rows nd after picking up from 22000 i
inserts lines.....why is that

thank

 

[Frank Kabel]

Hi
this macro should start at the end of your used range. Are there really
matching values behind row 22000
Try debugging this code and have a watch on RowNdX

 

[Gord Dibben]

This works for me.

Note the "row_index -1" change.

Inserts a blank row "below" the text "account"

The original code inserts a row above the "account" cell.

Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If LCase(Cells(row_index + 1, "A").Value) = "account" Then
Cells(row_index - 1, "A").EntireRow.Insert _
(xlShiftDown)
End If
Next
End Sub

Gord Dibben Excel MVP