New To Vb - Need Some Help With An Excel Macro - Topic

Member

Posts: 74,728

Joined: Oct 15 2004

Gold: 4,463.00

Jul 24 2012 08:13am

Hey guys,

I need hopefully short help with VBA. I'm working on a macro right now that will look at different columns of information, and delete the ones that duplicate. These are columns with seven unique pieces of data. I found a code online similar to what I want to do, but reversed to delete rows. What can I do to switch this code around to delete columns?

Code

Sub DeleteDups()

Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub

Since I'm unfamiliar with this type of code, also curious as to what this part of the line is trying to do:

Code

(Range("A1:A" & x), Range("A" & x).Text) > 1

Will send some FG your way for troubles. Thanks!

--M

This post was edited by Morbid. on Jul 24 2012 08:15am

carteblanche

Member

Posts: 32,925

Joined: Jul 23 2006

Gold: 3,804.50

Jul 24 2012 10:47am

Quote (Morbid. @ Jul 24 2012 10:13am)

Code

(Range("A1:A" & x), Range("A" & x).Text) > 1

fyi you should quote the full line, otherwise >1 won't make sense

Code

If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

i don't use VBA. i can give you my guess though.

countif(cells, criteria) -> counts how many cells meet a certain criteria

RANGE("A1:A" & X) -> check all the cells from the top
RANGE("A" & x) -> use the value inside this cell
> 1 -> more than one row matches (itself + one other)

so i'm guessing this checks every row to see if the previous row matches, then delete the row

if you wanna delete the column, i'd go row by row and check all the columns two at a time if they match. if they don't, then you can't delete the column so just return

for row = 1 to n
If Application.WorksheetFunction.CountIf(Range("A" & x), Range("B" & x).Text) = 0)
// return since the columns are not dupes
next row

// now we know A and B are the same. delete column B
Range("B" & x).EntireColumn.Delete

This post was edited by carteblanche on Jul 24 2012 11:10am

Pepega

Member

Posts: 74,728

Joined: Oct 15 2004

Gold: 4,463.00

Jul 24 2012 12:24pm

Yeah, I like the train of thought. I did try to do that.

It's the syntax that I can't do at the moment, because columns are defined from A to IV and that's harder to do once you transform the code. That's the part I'm trying to figure out.

carteblanche

Member

Posts: 32,925

Joined: Jul 23 2006

Gold: 3,804.50

Jul 24 2012 04:19pm

dont remember how characters are done in VB. can you just go from offset = 0 to n and hit column 'A' + offset? where 'A' is a unicode character, not string. so 'A' + 1 = 'B' as opposed to "A1"

dreu21952006

Member

Posts: 4,485

Joined: Mar 24 2006

Gold: 40.00

Jul 24 2012 09:13pm

Is A65536 seriouslly the last row? (I thought my datasheets were big...) Or is that part of the online example you found?

Additional info needed:

Are you looking for the script to remove ALL duplicates from the ENTIRE spreadsheet. (Meaning each cell will be unique throughout.)

Or simply, remove any duplicates contained within that column alone.

Pepega

Member

Posts: 74,728

Joined: Oct 15 2004

Gold: 4,463.00

Jul 25 2012 02:26am

Quote (dreu21952006 @ Jul 24 2012 10:13pm)

From a microscopic point of view, neither.

I want to remove duplicates within a single row. That's it.

Nothing cookie cutter, I've done a fair amount of searching. I just need the code up there to be reversed. Instead of searching for duplicates within a column and deleting rows, and I need it to search for duplicates within a row and delete columns.

dreu21952006

Member

Posts: 4,485

Joined: Mar 24 2006

Gold: 40.00

Jul 25 2012 11:50pm

'A' + 1 != 'B' --------------------------------- 'A' + 1 does not equal 'B'.
'A' + 1 = Error Type Mismatch ------------ 'A' + 1 will just give you an error.
'A'&1 = A1 ----------------------------------- This would simply be combining them.

This gets the corresponding Letter for a Number (number for a column. Like the 4th column = D etc.)

X = 1
LEFT(ADDRESS(1, x), 1) = A

X = 2
LEFT(ADDRESS(1, x), 1) = B

Etc.

And this was typed up in a sleep deprived state at home where I don't have Excel in order to test the code. If there are problems, which I'm expecting considering my need of sleep and the fact I can't find my glasses... Let me know. I'll fix it when I'm at work tomorrow where I have all of my resources.

Code

Sub DeleteDups()

Set lastcell = Cells.SpecialCells(xlLastCell)
lCols = lastcell.Column
letLCols = ADDRESS(1,lCols)

Dim x As Long
Dim y As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row

For x = 1 To LastRow
For y = 1 To lCols
If Application.WorksheetFunction.CountIf(LEFT(ADDRESS(1,y), 1)& x & ":" LEFT(ADDRESS(1,y), 1), Range(LEFT(ADDRESS(1,y), 1) & X).Text) > 1 Then
Range("A" & x).EntireColumn.Delete
End If
Next
Next x

End Sub

Logic = For each row, please check each columns corresponding cell for that row 1 by 1 to see if that cells contents exist anywhere else in the row and if they do we need to get rid of that column. Repeat as necessary, and then nothing further.

I was taught if you can't make a english statement with your code your doing it wrong :/.... old habits dying hard.

A good question would be... do you want the script to automatically scoot everything to the left to fill the empty space deleting the column is going to create? If do do the following:

Replace: Range("A" & x).EntireColumn.Delete

With this: Range("A" & x).EntireColumn.Delete Shift:=xlToLeft

Easy peasy if my memory serves me correct.

This post was edited by dreu21952006 on Jul 25 2012 11:58pm

dreu21952006

Member

Posts: 4,485

Joined: Mar 24 2006

Gold: 40.00

Jul 27 2012 08:03pm

In my drunken sleep deprived state, I tried making it a lot more complicated than it needed to be...

This works great. Tested and confirmed. Change the sub name as seen fit, I had it tied to a button for testing. No fg necessary.

The following code deletes the original column upon finding a match. (IE: In row 3 it finds a match when comparing column A and column B... It will delete column A.)

Code

Sub Button1_Click()
vLastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
vLastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

For x = 1 To vLastRow
For y = 1 To vLastCol
For Z = y + 1 To vLastCol
If Cells(x, y) = Cells(x, Z) Then
Cells(x, y).EntireColumn.Delete
vLastCol = vLastCol - 1
End If
Next
Next
Next
End Sub

The following code here does the opposite. It deletes the second column upon finding a match. (IE: In row 3 it finds a match when comparing column A and column B... It will delete column B.)

Code

Sub Button1_Click()
vLastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
vLastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

For x = 1 To vLastRow
For y = 1 To vLastCol
For Z = y + 1 To vLastCol
If Cells(x, y) = Cells(x, Z) Then
Cells(x, Z).EntireColumn.Delete
vLastCol = vLastCol - 1
End If
Next
Next
Next
End Sub

This post was edited by dreu21952006 on Jul 27 2012 08:09pm

dreu21952006

Member

Posts: 4,485

Joined: Mar 24 2006

Gold: 40.00

Jul 29 2012 12:30am

For the sake of learning, added what each line does.

Code

Sub Button1_Click() ' Rename this whatever you choose. Button1_Click is a macro I have tied to a button for testing purposes. Yours could be Sub DeleteStuff() of whatever.
vLastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row ' Quite simply put, the checks for the last used cell horizontally
vLastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column ' Same thing here, however vertically.

For x = 1 To vLastRow ' Simple For loop that will cycle from 1 to vLastRow (last known row). This loop ensures we check every row.
For y = 1 To vLastCol ' Simple For loop that will cycle from 1 to vLastCol (last known column.) This loop ensure we check each column for each row.
For Z = y + 1 To vLastCol ' Simple For loop that will cycle from the CURRENT Column (where the above loops current position is) onward to the last known column. See below for further reason.
If Cells(x, y) = Cells(x, Z) Then ' The first time this loop cycles, it checks the first cell (x, y) against the second cell (x, Z)... (Notice above Z = y + 1... current column + 1)
Cells(x, Z).EntireColumn.Delete ' If they match, we delete the corresponding column. This example shows us deleting the secondary column.
vLastCol = vLastCol - 1 ' The reason for this line... Since we deleted a column, our max number of columns just dropped by 1. Self explanitory.
End If ' Every If needs a End If.
Next ' Every For needs a Next. For 1 to 10 do whatever, next number, repeat, next number, repeat... until reaching the last number, next part of the program.
Next ' Same as above.
Next ' Save as above
End Sub ' Tell the script that code pertaining to this function is done. Every Sub needs a End Sub.

The logic of this script: (You can follow from top to bottom). For every row until the last, we want to check every column comparative to the last one checked, to see if a cell matches another in the same row. If these match, delete the column in which the second instance resides. Nothing further.

For learning purposes, I was taught to break down what you want your program to do in english... then code it as you wrote it. I've used that tip every time I've made a program/script.

Go Back To Programming & Development Topic List