vba clear contents

hoffies

New Member
Joined
Apr 2, 2009
Messages
19
Hi Gurus

(searched other posts but none made sense to me)

I dont know how to copy an excel sheet into the question.

But assuming the following text starts at CELL C2 (this is just a section of the workbook)

C D E F G

1 x 5 5 Correct!
2 x 5 5 Try Again?
3 x 5 5 Try Again?
4 x 5 5 Try Again?
5 x 5 5 Try Again?


This is a worksheet to test multiplication tables. I am trying to create a macro button to clear the contents of column F, for the kids to start again?

Please assist ? THANK YOU in advance!
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
The only bit that's even marginally difficult is telling the macro which bits to clear contents in.

A named range works well, or we can write a macro so that it works down the entire list, and then you can add extra questions below the current ones without needing to change anything else.

Which would you prefer?
 
Upvote 0
Without the named range and assuming that there will always be an entry in column C:

Code:
Sub Clear_Stuff()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, 3).End(xlUp).Row
Range("F2:F" & lastrow).ClearContents
End Sub

Dom
 
Upvote 0
Isn't
Code:
With ActiveSheet
    .Columns("f").ClearContents
End With
or
Code:
With ActiveSheet
    Intersect(.UsedRange, .Columns("f")).ClearContents
End With
good enough ?
 
Upvote 0
Isn't
Code:
With ActiveSheet
    .Columns("f").ClearContents
End With
or
Code:
With ActiveSheet
    Intersect(.UsedRange, .Columns("f")).ClearContents
End With
good enough ?

Maybe but the OP stated that it needs to start in F2 so I assume that there is a heading in Row 1.

Dom
 
Upvote 0
Yes, Thats correct Domski - Row 1 is in use. Dont want to clear that row, just the stuff below.

Basically, I named the range that I wanted cleared in column F and it worked fine with your code.

Thanks
 
Upvote 0
Basically, I named the range that I wanted cleared in column F and it worked fine with your code.

No need to name the range.

This might be better as well as the original code was dependent on there being an entry in column C which is not necessary either on reflection:

Code:
Sub Clear_Stuff()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, 6).End(xlUp).Row
Range("F2:F" & lastrow).ClearContents
End Sub

Dom
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top