Leave last col alone
David,
Difficult to test, but does this do it With Worksheets(i) cols = .UsedRange.Columns.Count Set Rng = .Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(-2, 0)) For Each c In Rng If c = old_Member Then Range(Cells(c.Row, "A"), Cells(c.Row, cols - 1)).ClearContents End If Next c Rng.Resize(, cols).Sort Key1:=.Range("A1"), Order1:=xlAscending End With -- HTH Bob Phillips "David Turner" wrote in message ... Using XL2000 I use the following code to delete a chosen member from a range on several sheets: With Worksheets(i) cols = .UsedRange.Columns.Count Set Rng = .Range(.Cells(3, 1), .Cells(Rows.Count, 1).End(xlUp).Offset(-2, 0)) For Each c In Rng If c = Old_Member Then .Rows(c.Row).ClearContents End If Next c Rng.Resize(, cols).Sort Key1:=.Range("A1"), Order1:=xlAscending End With I just discovered that it also clears a needed formula in the last column. How can I leave that column alone? -- David |
All times are GMT +1. The time now is 07:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com