Insert Row Before Red Cell / Delete Row After Yellow Cell

  • Good evening. I'm sure this is child's play for most of you, but I'm trying to simplify a horrible data entry task. I have a wealth of customer data that is not in a consistent format as it is copy/pasted for an html based source (some records are missing entries and thus throws off the number of "lines" each record has when pasted into Excel).


    I am trying to get a consistent number of blank lines for each record, as some of my housekeeping formulas are built around spaces, and the best way I THOUGHT to do that would be to insert a line above each red highlighted cell, and then remove a line underneath each yellow highlighted cell. Color coding certain cells was housekeeping for me to find inconsistencies in records.


    The first two row deletion loops based on text works fine. The code for inserting a row above the red highlighted cells is not, and I haven't yet tried my hand at deleting rows after a yellow highlight. Thanks in advance for any help.


    Here's what I so far:

  • Hey jolivanes, I've attached a mock file below that shows what I'm hoping to accomplish. I hope I made it clear in the attached example what I'm trying to accomplish. It may sound convoluted, but I think most of that is because of the inconsistency of the data I'm working with.

  • As per your example:

    A24 is Yellow and A25 is Red

    So this "it would look for any row with a red highlight and insert a row above"

    would result in

    A24 is Yellow, A25 is blank and A26 is Red

    And after that, "Lastly, it would find any rows with a yellow highlight and delete the blank row immediately below them."

    would result in

    A24 is Yellow and A25 is Red

    Is that not what you started out with?

    I must be missing some logic here somewhere.

  • jolivanes yes, that is an accurate understanding of the request. Really sorry if I left out critical information in the initial OP:

    All records have the red highlighted field, not all records have the yellow highlighted field. Those records that don't have the yellow field end up having less "rows" when copy/pasted. So for the purposes of this exercise, think of the Yellow field as # of Accounts. Some of our records have multiple accounts with us and some have none. If they have none, the site doesn't say 0, it just leaves that field completely blank.


    I need to have the same number of rows per record so that I can more easily manipulate the data with some formulas I've been working up. I've uploaded a new version that gives the name to the yellow highlights.

    It isn't technically ending up with the same as the beginning because not all records have the yellow field. Does that help clarify? Thanks again, truly, for engaging with me!

  • Like Roy said, use the text to delete rows.

    Code
    1. If InStr(Cells(i, 1), "Show me") <> 0 Or InStr(Cells(i, 1), "Extra level") <> 0 Then Cells(i, 1).EntireRow.Delete

    should work.

    But

    How far does this get you (try on a copy. Gone is gone forever.)

  • jolivanes thanks for that. I pasted the that second code directly into the test file I had uploaded. When I run the script, nothing appears to happen. No errors, but no rows inserted or deleted. So I played around a bit with it, and changed the ColumnIndex values to 1 (everywhere I saw a 3) based on my uneducated thought that a 3 would be referencing column C and not column A where the data is (is that a correct understanding of ColumnIndex values?). When I ran it after that change, it did delete the rows that had the text in them, but still did not insert/delete rows related to the color.


    Thoughts?

  • jolivanes thanks! I think this is on the right track, but I'm having issues with it. Does vbColor recongnize conditional formatting based highlights? I ask because it works fine on your sheet... it works fine when adding data to your sheet, but if I put conditional highlighting rules for changing the color of cells to red or yellow, the color related portions of your macro do nothing.

    I checked the rgb value of a cell that has been conditionally formatted to red and it returns #FFFFFF. I was surprised to see that. Is that normal for conditional formatting to actually be white even though the visible color is something else?

    I know how to assign a color to a cell using VBA generally, but not how to assign a color to a cell based on the text within the cell. So for example, every new record when pasted into my actual file, has "Select" and then the name of the Customer (all in the same line). So I want to highlight that cell that is the start of the new record (e.g., "Select Tom Smith") as vbRed. Does that make sense? What would that code look like?


    I think that if I assign the highlights using VBA instead of conditional formatting, it should circumvent this issue I'm seeing.


    Thanks in advance!

  • If I understood it right, this should do what you need.

  • jolivanes this is fantastic. So dang close and already really neat. Two things: since "account" can actually have instances of "accounts" (with the 's') how would I account (pun intended) for that? I thought maybe adding a comma and "accounts" after "account" as another string, but that gave me an error. Then I thought maybe just repeating that code line but with "accounts", but that didn't work.


    Second, deleting the row after a yellow highlight is not working but it really doesn't seem to make sense as to why (to me), since the inserting rows works fine and the only difference is the inclusion of the Offset(1) in the yellow related command (and if my basic knowledge is correct, that is Offset(1) is what is telling VBA to delete the row below).


    You have already been a fantastic help, and I really appreciate it.

  • If it is just the 2 possibilities, replace

    Code
    1. If InStr(Cells(i, 1).Offset(-1), "account") <> 0 Then

    with

    Code
    1. If InStr(Cells(i, 1).Offset(-1), "account") <> 0 Or InStr(Cells(i, 1).Offset(-1), "accounts") <> 0 Then


    Re: "deleting the row after a yellow highlight"

    I don't quite get this as that is a row we just inserted. If that empty row is not required, we don't need to insert.

    Or am I missing something obvious? (very much possible)


    BTW, it just inserts 4 cells wide instead of a whole row.

    You can increase the 4 to a higher number for more cells or If it needs to be a whole row, change accordingly.