Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Hi All,
I would like to loop thru a range of cells and delete the contents of the visible cells only. eg if the range is A1:D10 and column B is hidden all the cells will be deleted except B1:B10 I tried the following but the code fails where indicated. Sub testdelete() Dim rng As Range Dim mycell Set rng = Sheet1.Range("A1:D10") For Each mycell In rng If mycell.Hidden = False Then fails here mycell.Delete End If Next mycell End Sub Can anybody help please? Regards Michael Beckinsale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Sub testdelete()
Dim rng As Range Dim mycell As Range '<--FIXED Set rng = Sheet1.Range("A1:D10") For Each mycell In rng If mycell.EntireColumn.Hidden = False Then '<--FIXED mycell.Clearcontents '<--FIXED End If Next mycell End Sub Regards, Stefi €˛michael.beckinsale€¯ ezt Ć*rta: Hi All, I would like to loop thru a range of cells and delete the contents of the visible cells only. eg if the range is A1:D10 and column B is hidden all the cells will be deleted except B1:B10 I tried the following but the code fails where indicated. Sub testdelete() Dim rng As Range Dim mycell Set rng = Sheet1.Range("A1:D10") For Each mycell In rng If mycell.Hidden = False Then fails here mycell.Delete End If Next mycell End Sub Can anybody help please? Regards Michael Beckinsale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Hi Michael,
Try: '============= Public Sub Tester() On Error Resume Next Range("A1:D10").SpecialCells(xlCellTypeVisible). _ ClearContents On Error GoTo 0 End Sub '<<============= --- Regards, Norman "michael.beckinsale" wrote in message oups.com... Hi All, I would like to loop thru a range of cells and delete the contents of the visible cells only. eg if the range is A1:D10 and column B is hidden all the cells will be deleted except B1:B10 I tried the following but the code fails where indicated. Sub testdelete() Dim rng As Range Dim mycell Set rng = Sheet1.Range("A1:D10") For Each mycell In rng If mycell.Hidden = False Then fails here mycell.Delete End If Next mycell End Sub Can anybody help please? Regards Michael Beckinsale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Stefi / Norman, Many thanks, both work fine. In terms of speed / efficiency which code is the best? Regards Michael beckinsale |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Hi Michael,
Except for ranges with a very large number of non-contiguous visible cells, I would use the SpecialCells method. --- Regards, Norman "michael.beckinsale" wrote in message oups.com... Stefi / Norman, Many thanks, both work fine. In terms of speed / efficiency which code is the best? Regards Michael beckinsale |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Hi Michael,
Just to add, in the case of small ranges, I would not anticipate any perceptible difference in speed. --- Regards, Norman "Norman Jones" wrote in message ... Hi Michael, Except for ranges with a very large number of non-contiguous visible cells, I would use the SpecialCells method. --- Regards, Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Norman, Thanks. I have approx 4000 cells in the range so l will go with Cells.special method |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Hi Michael,
As far as the suggested SpecialCells method is concerned, a critical poinr might be reached with 8192+ non-contiguous areas, which would coorespond with a minimum of 16384 (= 8192*2) cells. - see the Microsoft KnowlegeBase Article # 83229: http://support.microsoft.com/kb/832293/en-us Given your range, this should not present a problem. However, the suggested code could be made more efficient by resticting the area of interest to the first column of your range, i.e.: '============= Public Sub Tester2() Dim CalcMode As Long With Application .ScreenUpdating = False CalcMode = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next Range("A1:D4000").Columns(1).SpecialCells _ (xlCellTypeVisible).EntireRow.ClearContents On Error GoTo 0 With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub '<<============= Note that to enhance speed, the suggested code turns off, and later restores, screen refreshing and automatic calculation. --- Regards, Norman "michael.beckinsale" wrote in message ups.com... Norman, Thanks. I have approx 4000 cells in the range so l will go with Cells.special method |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Hi Michael,
Replace that code with: '============= Public Sub Tester2() Dim rng1 As Range, rng2 As Range Dim CalcMode As Long Set rng1 = Range("A1:D4000") '<<==== CHANGE With Application .ScreenUpdating = False CalcMode = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next Set rng2 = rng1.Columns(1).SpecialCells _ (xlCellTypeVisible) Intersect(rng2.EntireRow, rng1.EntireColumn).ClearContents On Error GoTo 0 With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub '<<============= -- --- Regards, Norman "Norman Jones" wrote in message ... Hi Michael, As far as the suggested SpecialCells method is concerned, a critical poinr might be reached with 8192+ non-contiguous areas, which would coorespond with a minimum of 16384 (= 8192*2) cells. - see the Microsoft KnowlegeBase Article # 83229: http://support.microsoft.com/kb/832293/en-us Given your range, this should not present a problem. However, the suggested code could be made more efficient by resticting the area of interest to the first column of your range, i.e.: '============= Public Sub Tester2() Dim CalcMode As Long With Application .ScreenUpdating = False CalcMode = .Calculation .Calculation = xlCalculationManual End With On Error Resume Next Range("A1:D4000").Columns(1).SpecialCells _ (xlCellTypeVisible).EntireRow.ClearContents On Error GoTo 0 With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub '<<============= Note that to enhance speed, the suggested code turns off, and later restores, screen refreshing and automatic calculation. --- Regards, Norman "michael.beckinsale" wrote in message ups.com... Norman, Thanks. I have approx 4000 cells in the range so l will go with Cells.special method |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Norman, Thats great. All your input is gratefully appreciated. Regards Michael Beckinsale |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
is this a regular thing or a one-off?
if its a one-off, would it not be easier to use Edit Goto Special Visible cells only then delete the selected cells? hth, tim "michael.beckinsale" wrote in message ups.com... Norman, Thats great. All your input is gratefully appreciated. Regards Michael Beckinsale |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Hi Tim,
if its a one-off, would it not be easier to use Edit Goto Special Visible cells only If this were a one-off operation, I doubt that Michael would be seeking a programmatic solution and it is even less likely that he would be concerned with the speed and efficiency advantages of one coded approach over another. Perhaps, most telling of all, however, was the decision to post the question in the programming group. As this is a programming group, how does: Edit Goto Special Visible cells only differ from the initially advocated: Range("A1:D10").SpecialCells(xlCellTypeVisible).Cl earContents ? Except, perhaps, that the latter additionally defines the range and deletes the contents. --- Regards, Norman |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete contents of visible cells only
Hi Norman,
I agree with everything you've said. However, I've discovered on several occasions I have found peoples problems to be misinterpreted (because they fail to explain a vital bit of information) or tackled with VB because (as you say) its been posted in a programming newsgroup (which is the natural place to post the message when you *think* the solution must use VB). I know this to be the case because i am one of those people that has asked for a VBA solution to something that could have been achevied from just a few menu options; I'm not an Excel novice, but there are still some areas that i don't use very often, so i never think to use them and I would always choose a built in solution over having to create VBA to do the same job (in the right circumstances). There is nothing wrong with the code you provided, I'm merely suggesting an alternative that Michael may not have known existed. Having said all that, when i inevitably post a message looking for help, I would very thankful for any VBA code snippets you could provide! I think this is the most amazingly useful newsgroup i have ever seen. Regards, Tim "Norman Jones" wrote in message ... Hi Tim, if its a one-off, would it not be easier to use Edit Goto Special Visible cells only If this were a one-off operation, I doubt that Michael would be seeking a programmatic solution and it is even less likely that he would be concerned with the speed and efficiency advantages of one coded approach over another. Perhaps, most telling of all, however, was the decision to post the question in the programming group. As this is a programming group, how does: Edit Goto Special Visible cells only differ from the initially advocated: Range("A1:D10").SpecialCells(xlCellTypeVisible).Cl earContents ? Except, perhaps, that the latter additionally defines the range and deletes the contents. --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I only delete/clear the visible cells in a filtered list? | Excel Worksheet Functions | |||
Delete contents of cells | Excel Worksheet Functions | |||
Delete Contents of Cells | Excel Programming | |||
contents in cells are not visible when word wrap is selected | Excel Discussion (Misc queries) | |||
Delete specific cells contents in a row with some locked cells in the same row | Excel Programming |