Excel Macro; Delete row by looking up 2 different values 3

[EoinWALSH]

Hello,

I am 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 there are 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 - columnA = Line 3 - columnA then Delete row in between??

So in other words, there is a blank (coloured) line between each row, i just want this removed if the account numbers are the same, but not removed if their not the same?

Thanks in advance

Eoin Walsh

 

[SkipVought]

Hi,

"I have just written a macro that inserts a row between entries for formatting purposes."

Doing that has made your task more difficult! You will be plagued by the curse of non-contiguous data. You management ought to be ashamed to have imposed such an unreasonable restriction on you, as it seems.

It would have been a whole lot easier BEFORE destroying your table.

My recommendation is to sort the table by Account Number, delete the duplicates, then put your stupid empty rows back in with your macro, if you are so inclined.




Skip,

_[red][/red]

 

[bdmangum]

I agree with skip, this would be easier if you sort before you color the rows. Does your data start in sequencial rows? If so, then this will be easy, we'll just add in some conditions to adding the formatted row.

Selection.End(xlDown).Select

What is the "Selection" in above code? Meaning what cells are selected at that point? I'm assuming you are selecting the first cell and then grabbing the last cell.

Anyways this code should do what you need.

Sub sort()

Dim count As Long   'Row counter variable
Dim store
count = 2   'Sets initial row value

While Sheets(1).Cells(count, 1).Value <> ""
    store = Sheets(1).Cells(count, 1).Value 'Stores value for easier reference
    
    If store = Sheets(1).Cells(count - 1, 1).Value Then 'Compares to above value
        count = count + 1
    Else    'Runs when cell values are different
        With Sheets(1)
            .Cells(count, 1).EntireRow.Insert Shift:=xlDown
            .Cells(count, 1).RowHeight = 4.5
        End With
            
        With Sheets(1).Range(Cells(count, 1), Cells(count, 14)).Interior
            .ColorIndex = 15
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
        
        count = count + 2
    End If
Wend

End Sub

Oh, side note. Try to avoid using the "select" commands when possible. Using "select" is a not a good practice, there is usually a better way to accomplish your goal.

 

[EoinWALSH]

Thanks bdmangum,

Jsut got an error 1004, stating that "Excel cannot shift non blank cells off the work sheet". Think i know how to fix it.. I'll keep you updated..

Code
.Cells(count, 1).EntireRow.Insert Shift:=xlDown

 

[bdmangum]

Make sure the last few rows (65536 - however many rows you are inserting) do not contain data.

 

[SkipVought]

Guidelines for entering data on a worksheet

Microsoft Excel has a number of features that make it easy to manage and analyze data. To take advantage of these features, enter data in your worksheet according to the following guidelines.

Data organization

Put similar items in one column Design the data so that all rows have similar items in the same column.

Keep the range separate Leave at least one blank column and one blank row between the related data range and other data on the worksheet. Excel can then more easily detect and select the range when you sort, filter, or insert automatic subtotals.

Position critical data above or below the range Avoid placing critical data to the left or right of the range; the data might be hidden when you filter the range.

Show rows and columns Make sure any hidden rows or columns are displayed before making changes to the range. When rows and columns in a range are not showing, data can be deleted inadvertently.

Data format

Use formatted column labels Create column labels in the first row of the range of data. Excel uses the labels to create reports and to find and organize data. Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the range. Format the cells as text before you type the column labels.

Use cell borders When you want to separate labels from data, use cell borders— not blank rows or dashed lines— to insert lines below the labels.

Avoid blank rows and columns Avoid putting blank rows and columns in the range so that Excel can more easily detect and select the related data range.

Don't type leading or trailing spaces Extra spaces at the beginning or end of a cell affect sorting and searching. Instead of typing spaces, indent the text within the cell.

Extend data formats and formulas When you add new rows of data to the end of a data range, Excel extends consistent formatting and formulas. Three of the five preceding cells must use the same format for a format to be extended. All of the preceding formulas must be consistent for a formula to be extended.

List feature

You can designate a contiguous range of cells on your worksheet as a list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.). When you create a list, data defined by the list can be manipulated independently of data outside of the list. After you create a list, you can use list features to quickly sort, filter, total, or publish the data contained within the list.

You can also use the list feature to compartmentalize sets of related data by organizing that data using multiple lists on a single worksheet.

Skip,

_[red][/red]

 

[PHV]

Straight from your original post:
Do Until ActiveCell.Row = 2
[!] If Cells(ActiveCell.Row - 1, 1) <> Cells(ActiveCell.Row, 1) Then[/!]
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
[!] End If[/!]
'Move up one row.
ActiveCell.Offset(-1, 0).Select
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EoinWALSH]

Thanks a million PHV and everyone else...That worked perfectly i was trying to do a hidden field with a forumla but that worked so much easier!