Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is this type of VBA Logic possible? Delete Unique Row
Is this type of VBA Logic possible? I am trying to delete Unique Rows
after they have been hidden. Maybe I have approached this incorrectly.. I need to hide duplicate rows for a selected range, the code works great. I can select a range, run the macro and it hides the dups, I can subsequently copy /paste special "values" to get what I want. (I can't run the filter on the entire column, for other reasons) Here is the BUT- Once I run the macro on another selected range, it undoes the previous filter. (I don't want this, I would like for the previous filter to remain intact till I am done with the entire worksheet) So... I was thinking maybe I can just delete the "hidden/uniquie" records after they are hidden, I can't seem to get a return value of "True" for rngSrc.EntireRow.Hidden Is this possible? I hope someone knows how to do this, and can save me the headache! I grabbed a bit of the code from he http://www.cpearson.com/excel/DeleteDupsWithFilter.aspx ================================================== ============================ Sub RemoveDup() Dim myRng As Range Dim rngSrc As Range Dim ColumnNumber As Integer Dim ColumnLetter As String Dim firstRow As String Dim lastRow As String Set myRng = ActiveSheet.Range(ActiveWindow.Selection.Address) ColumnNumber = ActiveCell.Column ColumnLetter = ColLetter(ColumnNumber) With myRng firstRow = (.row - 1) lastRow = .Rows(.Rows.Count).row Set rngSrc = Range(ColumnLetter & firstRow, ColumnLetter & lastRow) rngSrc.AdvancedFilter Action:=xlFilterInPlace, Unique:=True Call DeleteHidden(rngSrc) End With ' ActiveSheet.ShowAllData End Sub Function ColLetter(ColNumber As Integer) As String ColLetter = Left(Cells(1, ColNumber).Address(True, False), 1 - (ColNumber 26)) End Function Sub DeleteHidden(rngSrc As Range) Dim DeleteRange As Range MsgBox rngSrc.EntireRow.Hidden If rngSrc.EntireRow.Hidden = True Then If DeleteRange Is Nothing Then Set DeleteRange = rngSrc.EntireRow Else Set DeleteRange = Application.Union(DeleteRange, rngSrc.EntireRow) End If End If DeleteRange.Delete shift:=xlUp ActiveSheet.ShowAllData End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need count of unique devices for each event type | Excel Discussion (Misc queries) | |||
Delete unique values from a row | Excel Discussion (Misc queries) | |||
Sum duplicates unique values and delete dupliques | Excel Worksheet Functions | |||
delete records with unique value | Excel Discussion (Misc queries) | |||
Delete unique records | Excel Programming |