| Home |
| Search |
| Today's Posts |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
need to make code more efficient (if possible)
That worked brilliantly. Thanks a bunch!
Bernie Deitrick wrote: See my comments in-line... HTH, Bernie MS Excel MVP Thanks very much! After a few tweaks it is sorting out the data and deleting the appropriate rows beautifully. There is still one small problem however- when I try to delete the helper columns, for some reason the rows do not delete (I still have the whole list). Also, the "G" column does not really delete, but keeps its header and the entire column is filled with #REF indicating some kind of formula error. Sounds like you are deleting the wrong column - but try my suggestions and see what happens.... Here is the relevant portion of my modified macro: With Workbooks(nwb).Worksheets(ws) myRow = .UsedRange.Rows.Count .Range("G1").Value = "Counter" .Range("G2").Formula = "=IF(C2=C1,1+G1,0)" .Range("G2").AutoFill Destination:=Workbooks(nwb).Worksheets(ws).Range(" G2:G" & myRow) .Range("H1").Value = "Keep/Delete" .Range("H2").Formula = "=IF(G3G2,""Delete"",""Keep"")" .Range("H2").AutoFill Destination:=Workbooks(nwb).Worksheets(ws).Range(" H2:H" & myRow) Try changing these two lines below: .Range(.Cells(2, 8), .Cells(.UsedRange.Rows.Count, 8)).Copy .Range(.Cells(2, 9), .Cells(.UsedRange.Rows.Count, 9)).PasteSpecial Paste:=xlValues, _ SkipBlanks:=False, Transpose:=False to: .Range("G:H").Copy .Range("G:H").PasteSpecial Paste:=xlValues and sort on H rather than I: key1:=Workbooks(nwb).Worksheets(ws).Range("H2"), _ .Cells.sort _ key1:=Workbooks(nwb).Worksheets(ws).Range("I2"), _ order1:=xlDescending, _ Key2:=NONE, _ Order2:=xlAscending, _ Header:=xlYes And instead of filtering on H, try Dim myF As Range Set myF = Range("H:H").Find("Keep") Range(myF, myF.End(xlDown)).EntireRow.Delete Remove this..... With .Range("I:I") .AutoFilter Field:=1, Criteria1:="Keep" .Cells(xlCellTypeVisible).EntireRow.Delete End With and try this for the column deletion: .Range("G:H").Delete |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| making code more efficient | Excel Discussion (Misc queries) | |||
| Efficient Code | Excel Programming | |||
| More Efficient code than this | Excel Programming | |||
| Making code more efficient | Excel Programming | |||
| More efficient code | Excel Programming | |||
Threaded Mode
