PDA

View Full Version : [SOLVED] Clear contents of cells



austenr
05-24-2005, 10:17 AM
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
05-24-2005, 10:20 AM
What do you mean by the correct way?

Generally to clear a range




Range("A1:A10").ClearContents



What code do you have already?

austenr
05-24-2005, 10:25 AM
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
05-24-2005, 10:30 AM
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
05-24-2005, 10:32 AM
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
05-24-2005, 10:35 AM
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
05-24-2005, 10:46 AM
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
05-24-2005, 10:47 AM
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 :whip. 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
05-24-2005, 10:49 AM
Thanks xld & Fire...xld got it for me. Thanks Solved

Zack Barresse
05-24-2005, 10:53 AM
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! :D

Bob Phillips
05-24-2005, 11:14 AM
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
05-24-2005, 11:49 AM
Well it didn't work in my test <G>

But point well given! :D

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

Bob Phillips
05-24-2005, 12:38 PM
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
05-26-2005, 06:30 AM
Also, a bit of evangelising :whip. 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?:dunno

Bob Phillips
05-26-2005, 08:57 AM
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) :dunno .

austenr
05-26-2005, 09:28 AM
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
05-26-2005, 09:50 AM
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
05-26-2005, 10:01 AM
Also, a bit of evangelising :whip. 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). :)

:think: 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! :rotlaugh:

Thanks for posting that, Bob! Good advice! :yes

johnske
05-26-2005, 10:07 AM
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
05-26-2005, 10:17 AM
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.

:hi:

Cyberdude
05-26-2005, 11:37 AM
kpuls, you struck a nerve. Lately I've been struggling with knowing when to use "long" instead of "integer". I habitually use integer, and now it looks like I should be using long.

all Integer's have to be converted to Long's
When would integer be preferable to using long, assumming memory space is not a consideration?

Ken Puls
05-26-2005, 11:59 AM
Honestly, Sid, the only time I can think of is if an arguement to a function specifically requires an Integer rather than a Long. Can't think of any off the top of my head right now though, but I know that there are some.

I pretty much use Long for everything now. :yes

austenr
05-26-2005, 01:19 PM
Hmmm..I tried both but neither seems to work....xld's does not compile...

austenr
05-26-2005, 07:00 PM
Johnske,
Sorry that I said your code didn't work. I was running it against the wrong sheet. :banghead:

Thanks! Works great! :thumb

johnske
05-26-2005, 08:26 PM
Hi Austen,

Glad to be able to help - but to get rid of all unnecessary items and to keep it PC for Ken :devil: (hi Ken - noted) I'll give a further mod here:Option Explicit

Sub CheckForNumeric()

Dim I As Long

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 SubRegards,
John

Ken Puls
05-26-2005, 10:37 PM
...and to keep it PC for Ken :devil:...

:rotlaugh:

austenr
05-27-2005, 09:42 AM
Well..I thought Johnske's code (see above) took care of the problem. After further testing I came across another obstacle. When you encounter a cell with contents as this:

5.00E+20 or this
147608130-111111

I does not work. I know that the bottom example is because it is not numeric which I thought I explained earlier but after a closer look I did not (sorry). As for the contents of 5.00E+20, will it not work on those kind of contents? Thanks

austenr
05-27-2005, 12:34 PM
Would it be easier to check to see if the format of the cell is numeric and if not change it to that? In other words is the first character is >=0 then format to numeric?

austenr
05-27-2005, 01:26 PM
Can someone help with this? Getting type mismatch.


Sub CheckForNumeric()
Dim newString As String
Dim I As Long

I = 1

With Worksheets("Sheet1")
For I = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
If I >= "A" And I <= "Z" Then
If IsNumeric(.Cells(I, "A").Value) Then
.Range("A" & I) = Right(.Range("A" & I), 6)
End If
End If
Next I
End With

End Sub

Bob Phillips
05-27-2005, 01:58 PM
Can someone help with this? Getting type mismatch.

You are testing a long against an alpha, no chance!

This line
If I >= "A" And I <= "Z" Then

should be replcaed by
If .Cells(I, "A").Value >= "A" And .Cells(I, "A").Value <= "Z" Then

But it still won't work because the folowing line is at odds with that line. First you test for a capital letter start, and then test for numeric. You can't have both.

austenr
05-27-2005, 02:00 PM
I got this to work for me:

Sub CheckForNumeric()

Dim newString As String
Dim I As Long

I = 1

With Worksheets("Sheet1")
For I = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
'If IsNumeric(.Cells(I, "A").Value) Then
If Trim(Sheets("Sheet1").Cells(I, 1).Value <= "9") Then
.Range("A" & I) = Right(.Range("A" & I), 6)
End If
Next I
End With

End Sub

Bob Phillips
05-27-2005, 02:02 PM
I got this to work for me:

Test with a value of 1ABCDEF!

austenr
05-27-2005, 02:04 PM
Not necessary. This works for me. I will never have that situation in the data I am testing.

johnske
05-27-2005, 07:20 PM
Hi Austen,

I thought we were testing for pure numerics. Not to worry, the ones with anything else in them, such as 1234567-987654321,147608130-111111 or even qwertiu123456789 can be handled with thisOption Explicit

Sub CheckForNumeric()
Dim I As Long
With Worksheets("Sheet2")
For I = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
If IsNumeric(Right(.Cells(I, "A"), 6)) Then
.Cells(I, "A") = Right(.Cells(I, "A"), 6)
End If
Next I
End With
End Sub
However for the ones such as 5.00E+20 you have a problem, I really don't know how you would handle that (?the last 6 digits would be all zeroes anyway?)

Regards,
John

austenr
05-27-2005, 08:40 PM
Yes you are correct. Might just delete them. Just junks it up a bit. But thanks for the code.