Macro Help - delete columns, remove duplicates, formatting

[max8719]

Hi All,

Im struggling with this, any ideas will be much appreciated.


I have a table A1 to M500.
I need to
switch columns L and M values
Then
delebte columns B, C and I
Then
replace duplicate values with blanks apart from the first instance in columns A then B then C.
Then
resize all columns to fit word legnth and apply all boarders to the table with a font of 16 through out.

Thanks in advance

 

[JLGWhiz]

This is constructed based on the original post, but excludes the Border formatting which I will leave to you.

Sub doStuff()
Dim sh As Worksheet
Set sh = ActiveSheet
    With sh
        .Columns("N").Insert
        .Columns("L").Cut .Range("N1")
        .Columns("L").Delete
        Union(Columns("B:C"), Columns("I")).Delete
        For i = 1 To 3
            For j = .Cells(Rows.Count, i).End(xlUp).Row To 2 Step -1
                If Application.CountIf(.Range(.Cells(1, i), .Cells(j, i)), Cells(j, i).Value) > 1 Then
                    .Cells(j, i).ClearContents
                End If
            Next
        Next
        .Columns("A:M").AutoFit
    End With
End Sub

I noted that by deleting only the cell in columns A, B and C which are duplicates or other cells, it will leave any data in columns, D:M intact for those rows. I assume you had considered that in your planning.

 

[max8719]

This is constructed based on the original post, but excludes the Border formatting which I will leave to you.

Sub doStuff()
Dim sh As Worksheet
Set sh = ActiveSheet
    With sh
        .Columns("N").Insert
        .Columns("L").Cut .Range("N1")
        .Columns("L").Delete
        Union(Columns("B:C"), Columns("I")).Delete
        For i = 1 To 3
            For j = .Cells(Rows.Count, i).End(xlUp).Row To 2 Step -1
                If Application.CountIf(.Range(.Cells(1, i), .Cells(j, i)), Cells(j, i).Value) > 1 Then
                    .Cells(j, i).ClearContents
                End If
            Next
        Next
        .Columns("A:M").AutoFit
    End With
End Sub

I noted that by deleting only the cell in columns A, B and C which are duplicates or other cells, it will leave any data in columns, D:M intact for those rows. I assume you had considered that in your planning.

Hi,

Would it be possible for the rows stay in the same order?

 

[JLGWhiz]

Hi,

Would it be possible for the rows stay in the same order?

They should be in the same order. The macro works from the bottom up. The only change should be that duplicates in columns A:C will be blank cells. If you mean columns, the only ones that change are the two you wanted swapped.

 

[JLGWhiz]

Adding to post # 4. Three columns were deleted entirely in addition to using an insert and delete to swap coloumns L and M. If your table has formulas which are dependent on any of the columns which were deleted or cells that were cleared of data, then the results may give the appearance of rows having been changed. But there was not any action that would change the original order of the rows. If you could post a screen shot of your sheet or a link and maybe give a more comprehensive description of your objective, perhaps I could give you a better solution.

 

[max8719]

It's working, thank you very much

 

[JLGWhiz]

It's working, thank you very much

You're welcome,
Regards, JLG