Need VBA Code for Adding / Deleting Rows

**sambuka** · 06-26-2012, 10:33 PM

Ok - I have 2 parts that I need for Adding / Deleting Rows to a formula based worksheet. (If you can find a simplier option, I would appreciate)

Part 1 - VBA CODE - Based on a value in a cell ("ADD")(Column R) - copy the row above and insert copied cells (shift cells down) - Need to loop until "NO ADD") (need help with formula for the cell to get "ADD" - I have ****=IF(AND(ISTEXT(A9),ISNUMBER(A8)),"ADD","NO ADD") **** However when new row is copied down I get ****=IF(AND(ISTEXT(A10),ISNUMBER(A8)),"ADD","NO ADD") **** Again the copying needs to loop but ISNumber(stays the same) needs to be one less than ISTEXT()

Part 2 - This is the code I have to delete the rows and it works

Sub DELETEROWS()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With ActiveSheet


        Firstrow = 5
        Lastrow = 500

        For Lrow = Lastrow To Firstrow Step -1

            With .Cells(Lrow, "Q")

                If Not IsError(.Value) Then

                    If .Value = "DELETE" Then .EntireRow.Delete
                    'This will delete each row with the Value "delete"
                    'in Column Q, case sensitive.

                End If

            End With

        Next Lrow

    End With

    
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

**JapanDave** · 06-26-2012, 10:37 PM

Can you please put you code in tags. And it would be much simpler if you could post a sample workbook to run through.

Cheers

**jeffreybrown** · 06-26-2012, 10:45 PM

Please add code tags as per forum rule #3 and how is this thread different than here?

Is this a duplicate of that thread?

**sambuka** · 06-26-2012, 11:00 PM

Thank you for the notice on the code tags. This new thread is a refinement on the older thread. I was able to figure out code to delete rows based on a given cell value, however I needed some help on adding and copying rows.

**sambuka** · 06-27-2012, 12:22 AM

I have been trying to use this code to add / copy / insert row. But not working


Sub INSERTROWS()
    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet



        'Set the first and last row to loop through
        Firstrow = 5
        Lastrow = 500

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the R column in this example
            With .Cells(Lrow, "R")

                If Not IsError(.Value) Then

                    If .Value = "ADD" Then ActiveCell.Offset(1, 0).EntireRow.Select Selection.Insert Shift:=xlDown
                    'This will delete each row with the Value "ADD"
                    'in Column R, case sensitive.
                    
                            
                            
                End If

            End With

        Next Lrow

    End With

    
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

**sambuka** · 06-27-2012, 11:14 AM

How about a code for : In a particular column (A) - if the previous cell is numeric then copy the previous row and paste insert down.

example

If
A3= 1234
A4= Text

Then copy row A3 and paste insert down

Can this be done?