Excel; Write VBA Macro to delete rows that contain same field value 1

[EoinWALSH]

Hello,

I looking to write a macro that deletes certain rows on a sheet. I have just written a macro that inserts a row between entries for formatting purposes.

However on some occasions their is two accounts listed with the same Account number. When this happens i want to keep the two accounts but delete the blank line in the middle which was just inserted for formatting. Heres the code that inserts the formatting line;

'Insert Blank rows with colour between each line

Selection.End(xlDown).Select

Do Until ActiveCell.Row = 2

ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.RowHeight = 4.5

ActiveCell.Columns("A:N").Select

With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

'Move up one row.
ActiveCell.Offset(-1, 0).Select
Loop

So basically i need something that says if Line 1 and line 3 are equal. Delete row in between??

Thanks in advance

Eoin Walsh

 

[SkipVought]

Hi,

1) This is not the correct forum for VBA solutions. Please post VBA code inquiries in Forum707.

2) This can be accomplished by sorting on the column in question, adding a helper column with a very simple formula to identify the duplicate rows, filter on the duplicate rows, Select the duplicate rows & DELETE.

3) You stated, "...inserts a row between entries for formatting purposes...." This is not a good practice as it DESTROYS the table. One of the attributes of a table is that ALL the data is contiguous. Rather, change the Row Height, format a border etc, but do NOT destroy your table by inserting empty rows. This is what novice spreadsheet uses unwittingly do to their detriment.

Skip,

_[red][/red]

 

[EoinWALSH]

Thanks for that Skipvought..The problem is with the rows i have just inserted but it has to be done because the people in Management are computer illiterate, and insist that a coloured rows are inserted between each entry, so its easier for them to read!

 

[SkipVought]

Excel HELP has descriptions and definitions of tables that you can show your management. You can quote me, for what it's worth, that inserting empty rows in a table is not considered best and accepted spreadsheet practice; it will ultimately COST you in time and accuracy to maintain.

Let's see if we get some other experienced contributors to comment on this.

As an alternative, you could add a helper column (hidden) with a formula that groups rows. Then use Format > Conditional Formatting to shade each group.

Skip,

_[red][/red]