Insert/Delete rows when a cell is filled/blank

nicolewh · Aug 7, 2009

[FONT=&quot]Hi,

I am a VBA beginner and very new to this website. I want to thank you in advance! Thanks. [/FONT]

[FONT=&quot]

Trying to create a friendly main worksheet for individuals when they open up the document. [/FONT]
[FONT=&quot]
Description with snapshots of my worksheet:[/FONT]

[FONT=&quot]I have a list of names in the A column in sheet1, starting at A3. The amount of names in this list change continually but right now the list ends at A7. Note: these listed cells, which contain these names, have formulas with text values (the formula includes the cells in the C column in sheet2). Also, right now, starting at B13 in sheet1 is a separate and different list. My hope is that if I filled in Sheet 2 (C13) that a new row will be created in sheet 1 (A10), and that everything below will all be shifted downward by one row. On the other hand, if I was to clear contents in sheet 2 (C12) then sheet1 row 9 will be deleted and the below rows will be shifted upward. [/FONT]

[FONT=&quot]I’ve included several links for you to see my spreadsheet – currently I’m unable to use the attachment feature. I know it may not be normal to do it this way but I am new and also known to be very detail oriented. [/FONT]
[FONT=&quot]Links:[/FONT]
[FONT=&quot]1.[/FONT][FONT=&quot]Current Sheet1[/FONT]
[FONT=&quot]2.[/FONT][FONT=&quot]Current Sheet2 [/FONT]
[FONT=&quot]3.[/FONT][FONT=&quot]Current Sheet1 with Formulas[/FONT]
[FONT=&quot]4.[/FONT][FONT=&quot]If add in sheet 2 [/FONT]
[FONT=&quot]5.[/FONT][FONT=&quot]Then add row with formula included[/FONT]
[FONT=&quot]6.[/FONT][FONT=&quot]If clear content in sheet2 [/FONT]
[FONT=&quot]7.[/FONT][FONT=&quot]Then delete row in sheet 1[/FONT]

[FONT=&quot]Some other facts:[/FONT]
[FONT=&quot]-Something tells me there has to be some point of reference in a cell below the list.[/FONT]
[FONT=&quot]-At some point there may be no names within the list – in this case there would be just a couple spaces in between both lists. On the other hand, there could be "unlimited" names in the A3 list of names. [/FONT]
[FONT=&quot]-I think the most difficult part is dragging the formula downward when a new row is created.[/FONT]
[FONT=&quot]
[/FONT]

[FONT=&quot]
I hope this was enough information. Please ask me any questions.

Thank you!

Nic[/FONT]

RolfJ · Aug 8, 2009

If I correctly understand your requirements this change event handler (if placed into the VBA section for Sheet2) should do the trick:

Code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        If Target.Value <> "" Then
            Dim dq As String
            dq = Chr$(34) & Chr$(34)
            Sheets("Sheet1").Rows(Target.Row).Insert Shift:=xlDown
            Sheets("Sheet1").Cells(Target.Row, Target.Column - 2).Formula = "=IF(Sheet2!C" & Target.Row & "=" & dq & "," & dq & ",Sheet2!C" & Target.Row & ")"
            Sheets("Sheet1").Cells(Target.Row, Target.Column - 1).Formula = "=IF(Sheet2!D" & Target.Row & "=" & dq & "," & dq & ",Sheet2!D" & Target.Row & ")"
        Else
            Target.Offset(0, 1).Value = ""
            Target.Offset(0, 2).Value = ""
            Sheets("Sheet1").Rows(Target.Row).Delete Shift:=xlUp
            If Target.Address <> Range("C5").End(xlDown).Address Then
                Rows(Target.Row).Delete Shift:=xlUp
            End If
        End If
    End If
End Sub

Hope this helped,
Rolf

nicolewh · Aug 8, 2009

Almost works perfectly. I just need one sharp tweak for it to be complete.
The only thing I need changed is how it is triggered. Let me explain.
The list in Column C in sheet2 have formulas which temporarily blank ("") values, ie cell [C9 =IF(x<x,x,"")] until they're no longer blank. When they're no longer blank, but a text value, I want this change (from ("") to Text) to trigger your code automatically. Any suggestions?

nicolewh · Aug 8, 2009

sorry, let me do that again.

Rolf,
Almost works perfectly. I just need one sharp tweak for it to be complete.
The only thing I need changed is how it is triggered. Let me explain.
The list in Column C in sheet2 have formulas which temporarily blank ("") values, ie cell [C9 =IF(x>x,x,"")] which are blank until the value becomes text. When this value changes (from ("") to text) that is when I want your code to begin automatically. Any suggestions?

Nicole

RolfJ · Aug 9, 2009

Hi Nicole:

not clear what you mean. My questions:

- what's the x in the formula?
- how does x get changed?

Looking forward to hearing back from you,
Rolf

nicolewh · Aug 9, 2009

Rolf,

Sorry, I didn't make the formula clear - that might just help you

Ok, here is a snapshot of the formulas in sheet2. The formulas in column C and D continue to go to row 100.

Thank you

Nicole

RolfJ · Aug 10, 2009

I am not sure why you would set up your Sheet2 that way, but it seems to me that you simply need to monitor column I (#9) instead of column C (#3). So this version of the macro I sent you previously should work just fine, unless I am still missing something:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub
    If Target.Column = 9 Then
        If Target.Value <> "" Then
            Dim dq As String
            dq = Chr$(34) & Chr$(34)
            Sheets("Sheet1").Rows(Target.Row).Insert Shift:=xlDown
            Sheets("Sheet1").Cells(Target.Row, Target.Column - 8).Formula = "=IF(Sheet2!C" & Target.Row & "=" & dq & "," & dq & ",Sheet2!C" & Target.Row & ")"
            Sheets("Sheet1").Cells(Target.Row, Target.Column - 7).Formula = "=IF(Sheet2!D" & Target.Row & "=" & dq & "," & dq & ",Sheet2!D" & Target.Row & ")"
        Else
            Target.Offset(0, 1).Value = ""
            Target.Offset(0, 2).Value = ""
            Sheets("Sheet1").Rows(Target.Row).Delete Shift:=xlUp
            If Target.Address <> Range("C5").End(xlDown).Address Then
                Rows(Target.Row).Delete Shift:=xlUp
            End If
        End If
    End If
End Sub

Hope this helped,
Rolf

nicolewh · Aug 10, 2009

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CBen%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CBen%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CBen%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:Wingdings; panose-1:5 0 0 0 0 0 0 0 0 0; mso-font-charset:2; mso-generic-font-family:auto; mso-font-pitch:variable; mso-font-signature:0 268435456 0 0 -2147483648 0;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> RolfJ,

I know I have been a bit confusing so sorry – but thanks for your patience.

Your right – I do have a column that is irrelevant (sheet2 Col: C). Anyway, Here is a copy of the column for I. It may be a bit confusing so let me explain. Column I changes its value by its IF-THEN formula which pretty much says that if the current time exceeds column H’s time then you get whats in column K. Btw- At some other point I hope to find some type of recalculation code that will update the time automatically. Whoo J

So, what I don’t want is to insert the names manually – it does it automatically after I place names and times etc. This is why I asked if the rows in sheet1 can be deleted/inserted by just a change of a value without me manually doing it. And that’s it. If you would like - you can skip the column I section and just go directly to sheet 1.

Column I
Column I with Formulas

Btw- Are these snapshots sufficient in explaining where I’m coming from?

Thank you so much!

Nicole

Insert/Delete rows when a cell is filled/blank

nicolewh

Well-known Member

Excel Facts

RolfJ

Banned

nicolewh

Well-known Member

nicolewh

Well-known Member

RolfJ

Banned

nicolewh

Well-known Member

RolfJ

Banned

nicolewh

Well-known Member

Similar threads

Forum statistics

Share this page

Insert/Delete rows when a cell is filled/blank

Well-known Member

Excel Facts

Banned

Well-known Member

Well-known Member

Banned

Well-known Member

Banned

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock