Clear contents of cells

[austenr]

What is the correct way to clear the contents of a range of cells based on the value in column A when using a loop or CASE statement? Thanks

[Norie]

What do you mean by the correct way?

Generally to clear a range

Range("A1:A10").ClearContents

What code do you have already?

[austenr]

ok..Say you have column A and you want to loop through looking for "PAGE" or "BILL" and if the string is there clear the contents of cells A thru F on that row.?

[Bob Phillips]

ok..Say you have column A and you want to loop through looking for "PAGE" or "BILL" and if the string is there clear the contents of cells A thru F on that row.?

Off the top, untested

Dim cLastRow As Long
Dim i As Long 
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cLastRow
If Cells(, "A").Value = "PAGE" Or _
Cells(i, "A").Value = "Bill" Then
Cells(i, "A").Resize(, 6).ClearContents
End If
Next

[Norie]

Perhaps something like this.

LastRow = Range("A65536").End(xlUp).Row

For I = 1 to LastRow

Set rng = Range("A" & I)

Select Case rng.Value

Case "PAGE", "BILL"

rng.Resize(1,6).ClearContents

Case Else

' Do nothing
End Select
Next I

[Zack Barresse]

Why loop?

Option Explicit

Sub ClearPageOrBill()
    Dim rngClear As Range
    Set rngClear = Range("A2:F" & Range("F65536").End(xlUp).Row)
    rngClear.AutoFilter field:=1, Criteria1:="=PAGE", Operator:=xlOr, Criteria2:="=BILL"
    Intersect(rngClear, rngClear.SpecialCells(xlCellTypeVisible)).ClearContents
End Sub

[Zack Barresse]

Two more things to add, for error checking, if you'd like.

1)

On Error Resume Next

Add this if you don't want to error out if no cells were found.

2)[

ActiveSheet.Cells.AutoFilter

Add this if you don't want AutoFilter to show when done. You can check for it at start if you'd like with some other code. This also assumes you will be running it on the activesheet, as no sheet is explicitly set.

[Bob Phillips]

Why loop?

Why Intersect?

Sub ClearPageOrBill()
Dim rngClear As Range
Set rngClear = Range("A1:F" & Cells(Rows.Count,"A").End(xlUp).Row)
rngClear.AutoFilter field:=1, Criteria1:="=PAGE", Operator:=xlOr, Criteria2:="=BILL"
rngClear.SpecialCells(xlCellTypeVisible).ClearContents
End Sub

You are better advised to get the row count from A, F might already be clear.

Also, a bit of evangelising . I am on a personal crusade to convince VBAers not to use

Range("A65536")

but to use

Cells(Rows.Count,"A")

instead. It looks better, you don't have to remember the number, and it caters for all Excel versions (remember 32767 rows).

[austenr]

Thanks xld & Fire...xld got it for me. Thanks Solved

[Zack Barresse]

Why Intersect

Good point. Force of habit I guess.

You are better advised to get the row count from A, F might already be clear.

Really dependent on the user. Six one, half a dozen the other.

Cells(Rows.Count,"A")

I like that. It does look better. The compatability cannot be refuted either.

(remmeber 32767 rows).

LOL!

[Bob Phillips]

Why intersect?

Just teasing, mimicing your reply (the sincerest form of flattery they say)

Really dependent on the user. Six one, half a dozen the other.

Well it didn't work in my test <G>

[Zack Barresse]

Well it didn't work in my test <G>

But point well given!

And one can't be faulted for setting up a pertinent negative. <vbg>

[Bob Phillips]

And one can't be faulted for setting up a pertinent negative. <vbg>

Yeah, I have a dilemma there. I seem to have a perspective that looks a skew at something, which makes me a good tester, but I haaaaate testing ..........

[Paleo]

Also, a bit of evangelising . I am on a personal crusade to convince VBAers not to use
Range("A65536")
but to use
Cells(Rows.Count,"A")
instead. It looks better, you don't have to remember the number, and it caters for all Excel versions (remember 32767 rows).

Hi xld,

very interesting approach! I must admit I always used Range("A65536") because always intendend to run my macros in XL 2003, but now I got a doubt. Talking about performance are they equal or is one approach faster than the other?

[Bob Phillips]

very interesting approach! I must admit I always used Range("A65536") because always intendend to run my macros in XL 2003, but now I got a doubt. Talking about performance are they equal or is one approach faster than the other?

Hi Paleo,

The Rows.Count will be faster, because it is using built-in constant values, VBA will calculate at start for the workbook, which will be quicker than parsing your code to get the number, convert it iand store it in memory, etc. But it will be so infinitesimal, no-one will notice, and it's not the sort of thing you are likely to do a million times in an application, which might then reap the benefits.

I do it more as a style thing, I just think it looks nicer.

Interestingly, from my old coding days I was always taught to declare constant variables, even for 0 and 1 for loops etc., and that is still good advice IMO in a serious app that is busy. In those sort of apps you should be aware of all performance savings. Interesting though because although I do it in VB, I don't in VBA (guess I don't write the big apps in VBA) .

[austenr]

I want to use this code to loop through column A and use the last 6 characters of the cell for its value only if it is a number and not purely alpha. Am having a problem getting it to skip the alpha only cells and those that are less than 10 characters. Also want to skip cells that contain +----" Any help would be appreciated.

Sub CheckForNumeric()
Dim newString As String
Dim I As Integer
I = 1
While Worksheets("Sheet2").Range("A" & I) > "0"
newString = Worksheets("Sheet2").Range("A" & I)
newString = Right(newString, Len(newString) - 10)
Worksheets("Sheet2").Range("A" & I) = newString
Wend
End Sub

Sample data attached.

[Bob Phillips]

I want to use this code to loop through column A and use the last 6 characters of the cell for its value only if it is a number and not purely alpha. Am having a problem getting it to skip the alpha only cells and those that are less than 10 characters. Also want to skip cells that contain +----" Any help would be appreciated.

Is this what you mean?

Sub CheckForNumeric()
Dim newString As String
Dim I As Integer
I = 1
With Worksheets("Sheet2")
For I = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If Len(.Cells(I, "A").Value) >= 10 Then
If IsNumeric(Right(.Cells(I, "A").Value)) Then
newString = .Range("A" & I)
newString = Right(newString, Len(newString) - 10)
.Range("A" & I) = newString
End If
End If
Next I
End With
End Sub

[Ken Puls]

Also, a bit of evangelising . I am on a personal crusade to convince VBAers not to use

Range("A65536")

but to use

Cells(Rows.Count,"A")

instead. It looks better, you don't have to remember the number, and it caters for all Excel versions (remember 32767 rows).

Like everyone else, it seems, I'm also intrigued by this. When was there only 32767 rows, Excel 5? Fortunately I came to programming a bit later than that! LOL! On the other hand, who knows when MS will give us more that 65536 rows too, so forward compatible code is good too!

This will mean I have to retrain myself now... sometimes hard to do!

Thanks for posting that, Bob! Good advice!

[johnske]

Hi xld, I could be wrong, but from reading what's said in the text I think he only needs something like this mod

Sub CheckForNumeric()
Dim newString As String
Dim I As Integer
I = 1
With Worksheets("Sheet2")
For I = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If IsNumeric(.Cells(I, "A").Value) Then
.Range("A" & I) = Right(.Range("A" & I), 6)
End If
Next I
End With
End Sub

[Ken Puls]

My turn for preaching!

Dim I As Integer

I would suggest using Long here, not Integer. Since all Integer's have to be converted to Long's, Long is faster (although maybe not noticeable). The real issue here, though, is that if there are more that 32767 rows, this will fail, as an Integer can only go that high. Long's can deal with numbers as high as 2,147,483,647, so give lots of room.