Help with inserting lines

V

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
 
F

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
 
A

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
 
F

Frank Kabel

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

vikram

Hi Frank,

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

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?
 
F

Frank Kabel

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

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
 
F

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
 
V

vikram

Hi Frank,,


I have checked it ....it is working if i pcopy some data from tha
sheet to another sheet but it does not work if i run it on my origina
sheet

probably my sheet is too big or something ...it jhas 35000 rows....

anywys thank u so much

i really apprecitae ur help and ur wonderful skill
 
V

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
 
F

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
 
G

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
 
Top