Solved: Clear Cells Except Cells with Formula

[Opv]

How can I clear all the content in a particular row except in the cells that have formulas.

Specifically, my range is range("$R4:$R4). Consequently, I would like to start in R4 and proceed to the right until the last cell with either a value or a formula and clear ONLY those cells that contain a value.

Thanks,

Opv

[mdmackillop]

Select your range, press F5, select special and tick the Constants options. You can then delete all selected cells. If you wish a macro, record it while doing these operations.

[Opv]

Select your range, press F5, select special and tick the Constants options. You can then delete all selected cells. If you wish a macro, record it while doing these operations.

Thanks. Now can you tell me why the resulting macro is clearing all data on the spreadsheet rather than just on Row 6?

The only thing I changed in the recorded macro is the original range, which was the actual specific range of cells that I originally highlighted. I was trying to incorporate the ".End(xlToRight).Select" to make the code work in the event one or more new columns of data are added.


Sub ClearValuesOnly()
'
' ClearValuesOnly Macro
' Macro recorded 3/20/2010 by
'
'
Range("g6").End(xlToRight).Select
Range("G6").Activate
Selection.SpecialCells(xlCellTypeConstants, 23).Select
' Selection.ClearContents
End Sub

[Paul_Hossler]

I don't think the 1st and 2nd lines are doing what you think they do

Something like this might get you started


Option Explicit
Sub ClearRowExceptFormulas()
Dim rStart As Range, rLast As Range, rConstants As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set rStart = Selection.Rows(1).EntireRow.Cells(1, 1)
Set rLast = Selection.Rows(1).EntireRow.Cells(1, Selection.Parent.Columns.Count).End(xlToLeft)
Set rConstants = Nothing
On Error Resume Next
Set rConstants = Range(rStart, rLast).SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rConstants Is Nothing Then
Call rConstants.ClearContents
End If
End Sub


There are a number of consolidations and simplifactions that could be made, but I tend to be wordy in most instances

Paul

[Opv]

I don't think the 1st and 2nd lines are doing what you think they do

Something like this might get you started


Option Explicit
Sub ClearRowExceptFormulas()
Dim rStart As Range, rLast As Range, rConstants As Range

If Not TypeOf Selection Is Range Then Exit Sub

Set rStart = Selection.Rows(1).EntireRow.Cells(1, 1)
Set rLast = Selection.Rows(1).EntireRow.Cells(1, Selection.Parent.Columns.Count).End(xlToLeft)
Set rConstants = Nothing
On Error Resume Next
Set rConstants = Range(rStart, rLast).SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rConstants Is Nothing Then
Call rConstants.ClearContents
End If
End Sub

There are a number of consolidations and simplifactions that could be made, but I tend to be wordy in most instances

Paul

Thanks. Unless I am missing something, the suggested code is doing the same thing as the one I posted. I just tested it and it is clearing all cells on the sheet except those that have a formula. I am only wanting to effect the data cells in Row 6.

Opv

[mdmackillop]

Rows("6:6").SpecialCells(xlCellTypeConstants, 23).ClearContents

'or
Range(Cells(6, "G"), Cells(6, "G").End(xlToRight)).SpecialCells(xlCellTypeConstants, 23).ClearContents

[Opv]

Rows("6:6").SpecialCells(xlCellTypeConstants, 23).ClearContents

'or
Range(Cells(6, "G"), Cells(6, "G").End(xlToRight)).SpecialCells(xlCellTypeConstants, 23).ClearContents




That did the trick. Thanks a million.

Opv

[mikerickson]

If aRange is a single cell, aRange.SpecialCells(xlConstants) returns the same range as Cells.SpecialCells(xlConstants)

Try
With Range("R4")
With Range(.Cells, .End(xlToRight))
Application.Intersect(.Cells,.SpecialCells(xlCellTypeConstants)).ClearConte nts
End With
End With

[Paul_Hossler]

Opv -- glad you got it working. My code was clearing the first row in the Selection, so if you wanted to clear Row 6, then select row 6, etc.

The problem with using End(xlToRight) is that it will go to the first non-empty cell from where you start. If there is data after that, it will be missed.

Starting from the far right side of the worksheet and using xlToLeft it will find the last cell with data on it

Paul

[Opv]

If aRange is a single cell, aRange.SpecialCells(xlConstants) returns the same range as Cells.SpecialCells(xlConstants)

Try
With Range("R4")
With Range(.Cells, .End(xlToRight))
Application.Intersect(.Cells,.SpecialCells(xlCellTypeConstants)).ClearConte nts
End With
End With

That works too. Thanks.

[Opv]

Opv -- glad you got it working. My code was clearing the first row in the Selection, so if you wanted to clear Row 6, then select row 6, etc.

The problem with using End(xlToRight) is that it will go to the first non-empty cell from where you start. If there is data after that, it will be missed.

Starting from the far right side of the worksheet and using xlToLeft it will find the last cell with data on it

Paul

So much to learn. I should have started much younger. Thanks.

[mdmackillop]

Starting from the far right side of the worksheet and using xlToLeft it will find the last cell with data on it

Paul

as in

Range(Cells(6, "G"), Cells(6, Columns.Count).End(xlToLeft)) _
.SpecialCells(xlCellTypeConstants, 23).ClearContents