Excel VBA to insert/delete rows at end of range

Question

I need to insert or delete some rows depending on what a variable states.

Sheet1 has a list of data. With sheet2 which is formatted, i want to copy that data so sheet2 is just a template and sheet1 is like a user form.

What my code does up until the for loop is get the number of rows in sheet 1 which only contains data and also the number of rows in sheet2 which contains data.

If the user adds some more data to sheet1 then i need to insert some more rows at the end the data in sheet2 and if the user deletes some rows in sheet1 the rows are deleted from sheet2.

I can get the number of rows on each so now how many to insert or delete but that's where i've come unstuck. How would I insert/delete the correct amount of rows. Also i wanted to alternate the rows colours between white and grey.

I did think that it might be an idea to delete all the rows on sheet2 then insert the same amount of rows that are in sheet1 using the alternating row colours but then again i did see something about using mod in the conditional formatting.

Can anyone please help?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim listRows As Integer, ganttRows As Integer, listRange As Range, ganttRange As Range
    Dim i As Integer


    Set listRange = Columns("B:B")
    Set ganttRange = Worksheets("Sheet2").Columns("B:B")

    listRows = Application.WorksheetFunction.CountA(listRange)
    ganttRows = Application.WorksheetFunction.CountA(ganttRange)

    Worksheets("Sheet2").Range("A1") = ganttRows - listRows

    For i = 1 To ganttRows - listRows
        'LastRowColA = Range("A65536").End(xlUp).Row


    Next i

    If Target.Row Mod 2 = 0 Then
        Target.EntireRow.Interior.ColorIndex = 20
    End If

End Sub

Upload a sample file in www.wikisend.com and then share the link here. Ensure that the file doesn't contain any confidential data. You can alternatively upload screeenshots of both sheets. — Siddharth Rout
– Siddharth Rout, Commented May 24, 2012 at 14:18
I've tried uploading the spreadsheet like you said but it threw an HTTP500 error. Is there a way i can upload some screenshots? — AdRock
– AdRock, Commented May 24, 2012 at 14:33
Rather than checking to see if you need to change the row color after every worksheet change, you can select the columns used, then choose "New Rule" from the Conditional Formatting icon. Use the following formula: =MOD(ROW(),2)=0, then Format -> Fill with the appropriate color. — Zairja
– Zairja, Commented May 24, 2012 at 15:21

Scott Holtzman · Accepted Answer · 2012-05-24 14:56:20Z

1

I didn't test this, because I didn't have sample data, but try this out. You may need to change some of the cell referencing to fit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim listRows As Integer, ganttRows As Integer, listRange As Range, ganttRange As Range
    Dim wks1 As Worksheet, wks2 As Worksheet

    Set wks1 = Worksheets("Sheet2")
    Set wks2 = Worksheets("Sheet1")

    Set listRange = Intersect(wks1.UsedRange, wks1.columns("B:B").EntireColumn)
    Set ganttRange = Intersect(wks2.UsedRange, wks2.columns("B:B").EntireColumn)

    listRows = listRange.Rows.count
    ganttRows = ganttRange.Rows.count

    If listRows > ganttRows Then 'sheet 1 has more rows, need to insert
        wks1.Range(wks1.Cells(listRows - (listRows - ganttRows), 1), wks1.Cells(listRows, 1)).EntireRow.Copy 
       wks2.Cells(ganttRows, 1).offset(1).PasteSpecial xlPasteValues
    ElseIf ganttRows > listRows 'sheet 2 has more rows need to delete
        wks2.Range(wks2.Cells(ganttRows, 1), wks2.Cells(ganttRows - (ganttRows - listRows), 1)).EntireRow.Delete
    End If

    Dim cel As Range
    'reset range because of updates
    Set ganttRange = Intersect(wks2.UsedRange, wks2.columns("B:B").EntireColumn)

    For Each cel In ganttRange
        If cel.Row Mod 2 = 0 Then cel.EntireRow.Interior.ColorIndex = 20
    Next

End Sub

UPDATE

Just re-read this line

If the user adds some more data to sheet1 then i need to insert some more rows at the end the data in sheet2 and if the user deletes some rows in sheet1 the rows are deleted from sheet2.

My solution is based on if the user insert / deletes rows at the bottom of the worksheet. If the user inserts / deletes rows in the middle, you are better off copy the entire range from sheet1 and onto a cleared out sheet2.

edited May 24, 2012 at 14:56

answered May 24, 2012 at 14:25

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AdRock Over a year ago

I wouldn't think a row would need to be deleted. This does the job anyway

Scott Holtzman Over a year ago

I wouldn't think a row would need to be deleted -> see and if the user deletes some rows in sheet1 the rows are deleted from sheet2 in your original post. Also, i just edited code, to paste only values into Sheet, too preserve formatting.

Zairja Over a year ago

I believe it's inefficient to use the For loop to apply formatting. Plus, previously formatted rows may be messed up by later deletions / insertions. My comment for the question is one solution. This is the VBA method (new line marked by /):

Dim r As Range / Set r = Sheet1.Range("A:E") /   r.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0" /  r.FormatConditions(r.FormatConditions.Count).SetFirstPriority /  r.FormatConditions(1).Interior.Color = 255 /  r.FormatConditions(1).StopIfTrue = False

This would only be run once on the particular sheet.

Scott Holtzman Over a year ago

@Zairja - +1 for great comment. Very true. and I agree.

Collectives™ on Stack Overflow

Excel VBA to insert/delete rows at end of range

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related