[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Clear contents through VBA for Excel specific Columns

Posted on 2012-09-07
51
Medium Priority
?
2,188 Views
Last Modified: 2012-09-18
I have an Excel Worksheet that requires specific Columns to be cleared. Below is the VBA code I am using... this works fine for Column C and Column E.

However, in column B only cells with the following values ("NMC" or "PMC) should be changed to "FMC" respectively. All cells that are of value "" will not be changed. A pull-down menu restricts the user from using any other value than the three mentioned above. The code stops on the "IF" statement and I'm not sure how to resolve the error.

Also, the first row is the Header and the Sheet is protected.


Sub ResetColumns1_Click()

    '''' Change Status to FMC
    Range("B:B").Select
    Range(Selection, Selection.End(xlDown)).Select
        If Selection.Value = "NMC" Or Selection.Value = "PMC" Then
            Selection.Value = "FMC"
        End If
       
    '''' Clear Start Time
    Range("C:C").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Value = ""
       
    '''' Clear Comments
    Range("E:E").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Value = ""
   
End Sub
0
Comment
Question by:edrz01
  • 21
  • 17
  • 6
  • +1
50 Comments
 
LVL 10

Expert Comment

by:etech0
ID: 38377795
When the code stops, what error do you get?
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38377802
What error do you get?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38377816
Sub ResetColumns1_Click()

    '''' Change Status to FMC
    Range("B:B").replace "NMC","FMC"
    Range("B:B").replace "PMC","FMC"
       
    '''' Clear Start Time
    Range("C:C").clearcontents
       
    '''' Clear Comments
    Range("E:E").clearcontents
   
End Sub
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 10

Expert Comment

by:etech0
ID: 38377824
Note: that will also clear your column headers in C and E. If that's a problem, we can avoid that.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38377829
Dim LastRowColB As Long
Dim lngIndex As Long

LastRowColB = Range("B65536").End(xlUp).Row
For lngIndex = 1 To LastRowColB
    If Range("B" & lngIndex).Value = "NMC" Or Range("B" & lngIndex).Value = "PMC" Then
        Range("B" & lngIndex).Value = "FMC"
    End If
Next

Open in new window

0
 

Author Comment

by:edrz01
ID: 38377832
Runtime error '13'
Type mismatch

is the error
0
 
LVL 10

Expert Comment

by:etech0
ID: 38377839
Makes sense. Try both code options that were posted, and let us know what happens.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38377841
Okay try my or etech0's code/
0
 
LVL 10

Expert Comment

by:etech0
ID: 38377847
MartinLiss: great minds think alike...
BTW: wasn't me, it was nutsch who posted the other code.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38377854
Right - bad eyesight.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38377856
No prob
0
 

Author Comment

by:edrz01
ID: 38377871
I have tried nutsch code, but since the 'Sheet' is protected the code will stop on:
    '''' Clear Start Time
    Range("C:C").clearcontents
       
    '''' Clear Comments
    Range("E:E").clearcontents

Due to the first row being protected. However, the first part does work because it is looking for a value.

Will continue to use the original code for the Start Time and Clear Comments unless someone has another solution...
0
 

Author Comment

by:edrz01
ID: 38377876
BTW... etch, row 1 cannot be changed as it is the header.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38377901
Using original code should be fine.

You could also do:

    '''' Clear Start Time
    Range(Range("C2"), Range("C2").End(xlDown)).Value = ""

Open in new window


and the same for E
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38377907
Change line 5 in my code to

For lngIndex = 2 To LastRowColB
0
 

Author Comment

by:edrz01
ID: 38377940
Ok... new problem cropped up... I made the changes above, but since The Workseet_Change was sensed... when the Value = "" in the second procedure. The Target.Value stopped the code from proceeding to Line E2

'''' Insert Date/Time Stamp only on Row/Cell affected'''
Private Sub Worksheet_Change(ByVal Target As Range)
----->LINE CODE STOPS HERE:---> AT THE 'IF' STATEMENT
    If Target.Value = "NMC" Then
        Target.Cells(1, 2).Value = Now()
    ElseIf Target.Value = "FMC" Or Target.Value = "PMC" Then
        Target.Cells(1, 2).Value = "" 'Start Time
        Target.Cells(1, 4).Value = "" 'Comments
    End If
End Sub

Sub ResetColumns1_Click()
    '''' Change Status to FMC
    Range("B:B").Replace "NMC", "FMC"
    Range("B:B").Replace "PMC", "FMC"
   
    '''' Clear Start Time
    Range(Range("C2"), Range("C2").End(xlDown)).Value = ""
    'Range("C:C").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.Value = ""
   
    '''' Clear Comments
    Range(Range("E2"), Range("E2").End(xlDown)).Value = ""
    'Range("E:E").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Selection.Value = ""
   
End Sub
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38377950
You can use a modification of the Intersect method shown below

If Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
            MsgBox "The active cell does NOT Intersect A1:A10"
0
 
LVL 10

Expert Comment

by:etech0
ID: 38377952
Do you really want that code to run every time anything changes? Maybe you want to run it with a button?
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 1000 total points
ID: 38377954
add events management

Sub ResetColumns1_Click()

application.enableevents=false

    '''' Change Status to FMC
    Range("B:B").Replace "NMC", "FMC"
    Range("B:B").Replace "PMC", "FMC"
   
    '''' Clear Start Time
    Range(Range("C2"), Range("C2").End(xlDown)).Value = ""
   
    '''' Clear Comments
    Range(Range("E2"), Range("E2").End(xlDown)).Value = ""

   application.enableevents=true
End Sub 

Open in new window


Thomas
0
 

Author Comment

by:edrz01
ID: 38378053
nutsch and etcho you are both going to receive points...your solutions are great. But before I close out the question I have another peculiar instance. I have run your codes and they do work… what does happen is Column B reverts to FMC for all instances… BUT, the Column C or Column E are cleared below the Comments row… not all the cells are cleared, unless I press the Reset Columns button several times. Just a quirk… If I fill all the 92 cells that contain data, then all cells are cleared. But if only a few here and there have values… only a portion of Column C and/or a portion of Column E have values that remain…it’s not consistent. So I have to press reset several times to clear all values. I’ve tried several variances and the quirk remains.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38378138
That's probably because there are blank cells in the column. To fix it, change

Range(Range("C2"), Range("C2").End(xlDown)).Value = ""

Open in new window


to

Range(Range("C2"), Range("C65536").End(xlUp)).Value = ""

Open in new window


and the same for E
0
 

Author Comment

by:edrz01
ID: 38378181
etech0, I applied your fix... but get the error 1004
"The cell or chart that you are trying to change is protected and therefore read-only" see attachment if necessary.
Error1004.jpg
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38378188
is the sheet protected by a password?
0
 

Author Comment

by:edrz01
ID: 38378190
yes it is... I have to protect all other columns.
0
 

Author Comment

by:edrz01
ID: 38378194
I also have to protect the Header Information
0
 
LVL 10

Expert Comment

by:etech0
ID: 38378196
In column C, what's the last (lowest down) cell that's unprotected?
Replace C65536 in my previous post with that cell.
0
 
LVL 39

Assisted Solution

by:nutsch
nutsch earned 1000 total points
ID: 38378206
Try this code, or just unprotect the whole columns C and E, etech0's code should work if the whole column in unlocked.

Sub ResetColumns1_Click()
dim rgCell as range

application.enableevents=false

    '''' Change Status to FMC
    Range("B:B").Replace "NMC", "FMC"
    Range("B:B").Replace "PMC", "FMC"
   
    '''' Clear Start Time
for each rgcell in Range(cells(2,3), cells(rows.count,3).End(xlup)).specialcells(xlcelltypeconstants)
    if rgcell.locked=false then rgcell.clearcontents
next 

    '''' Clear Comments
for each rgcell in Range(cells(2,5), cells(rows.count,5).End(xlup)).specialcells(xlcelltypeconstants)
    if rgcell.locked=false then rgcell.clearcontents
next 

   application.enableevents=true
End Sub 

Open in new window

0
 

Author Comment

by:edrz01
ID: 38378211
nutsch... this will clear the Header Information if unlocked won't it?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38378218
nope, it starts on row 2, just like etech0's code.

Thomas
0
 

Author Comment

by:edrz01
ID: 38378257
nutsch and etch0.... we'll pick this up Monday... have to shutdown for the weekend now.
0
 

Author Comment

by:edrz01
ID: 38383040
nutsch and etch0 - The code below still renders a protection error. Data entries begin on Row 3; Column 3 and Column 5 are to be cleared. When I looked at the variables stored, the following is what I see.
++++++++++++++++++++++
rgCell = nothing
Cells(3,3) = 9/15/12
xlUp = -4162
xlCellTypeConstants = 2
++++++++++++++++++++++
At least I know that it is reading Row 3 and Cell C3. Not sure why it will not clear the contents of that cell or the columns below it. I have double check to make sure all Cells C3 and below are not Protected.

I did Unprotect the entire sheet and 'stepped' through the code and found that yes C3 and below did clear the contents. And did not affect the Header on Row 2 nor the Row 1. But re-protecting the sheet...the protection error returns.

''''Clear Contents and reset Status''''
Sub ResetColumns1_Click()
Dim rgCell As Range

Application.EnableEvents = False
    '''' Change Status to FMC
    Range("B:B").Replace "NMC", "FMC"
    Range("B:B").Replace "PMC", "FMC"

    '''' Clear Start Time
For Each rgCell In Range(Cells(3, 3), Cells(Rows.Count, 3).End(xlUp)).SpecialCells(xlCellTypeConstants)
    If rgCell.Locked = False Then rgCell.ClearContents
Next

    '''' Clear Comments
For Each rgCell In Range(Cells(3, 5), Cells(Rows.Count, 5).End(xlUp)).SpecialCells(xlCellTypeConstants)
    If rgCell.Locked = False Then rgCell.ClearContents
Next

   Application.EnableEvents = True
End Sub
0
 
LVL 10

Assisted Solution

by:etech0
etech0 earned 1000 total points
ID: 38383147
Sounds like a cell is protected when it shouldn't be.

Let's figure out which one.

Run this code when the sheet is not protected:

Sub Testing()
Range(Cells(3, 3), Cells(Rows.Count, 3).End(xlUp)).select
End sub

Open in new window


Then, go into Format Cells for them all (you can do control-1), and make sure that by Protection, Locked is empty. (If it's not then make it.)

Then do the same thing for column E, with this code:

Sub Testing()
Range(Cells(3, 5), Cells(Rows.Count, 5).End(xlUp)).select
End sub

Open in new window


Let us know what happens!
0
 

Author Comment

by:edrz01
ID: 38383292
ok I ran your script on the Columns and received no Errors Unprotected and Protected...
0
 

Author Comment

by:edrz01
ID: 38383305
I also ensured ALL rows had data from C3 to C101 and E3 to E101...
0
 
LVL 10

Expert Comment

by:etech0
ID: 38383308
Hmm. Did you check if all of the cells that my script selects are unlocked? (via format cells)

On which line in the code do you get the protection error?
0
 

Author Comment

by:edrz01
ID: 38383350
Ok... this time I placed your code in the ResetColumns1_Click() and I enabled the Debug.

As I stepped through the procedure, your top two lines ran and highlighted the respective rows with data...no errors.

Next, I stepped to the SpecialCells line and in both cases to protection error is displayed.

Could it be the rgCell or the .SpecialCells??

Range(Cells(3, 3), Cells(Rows.Count, 3).End(xlUp)).Select
Range(Cells(3, 5), Cells(Rows.Count, 5).End(xlUp)).Select

For Each rgCell In Range(Cells(3, 3), Cells(Rows.Count, 3).End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each rgCell In Range(Cells(3, 5), Cells(Rows.Count, 5).End(xlUp)).SpecialCells(xlCellTypeConstants)
0
 
LVL 10

Expert Comment

by:etech0
ID: 38383374
Did you check if any of the cells that my script selected are locked? Because it could be that selecting them works, but editing them does not.

When the cells are selected, stop the debugger and check if they are protected.
0
 

Author Comment

by:edrz01
ID: 38383446
Ok, I did another test and ensured the Check mark for Protected was removed for the ENTIRE Column C, but left Column E as is. Re-protected the Sheet and ran... Protection error returned. Looking at the properties for the Sheet: Only have a check mark next to Select unlocked cells. Should the Select locked cells 'have' a check mark??? I removed that check mark so the user could not put their cursor into a locked cell.
0
 
LVL 10

Expert Comment

by:etech0
ID: 38383466
On which part of the code do you get the protection error now?
0
 

Author Comment

by:edrz01
ID: 38383477
Both...
For Each rgCell In Range(Cells(3, 3), Cells(Rows.Count, 3).End(xlUp)).SpecialCells(xlCellTypeConstants)

For Each rgCell In Range(Cells(3, 5), Cells(Rows.Count, 5).End(xlUp)).SpecialCells(xlCellTypeConstants)

I also removed the check mark entirely for both Columns C & E... both receive errors.
0
 
LVL 10

Assisted Solution

by:etech0
etech0 earned 1000 total points
ID: 38383504
Ohh!

Try changing removing from .SpecialCells to the end of the line, and see if it helps.
0
 

Author Comment

by:edrz01
ID: 38383520
BINGO!!!! that works... but could I not just put ClearContents on the end of:

Range(Cells(3, 3), Cells(Rows.Count, 3).End(xlUp)).ClearContents
Range(Cells(3, 5), Cells(Rows.Count, 5).End(xlUp)).ClearContents

and not use the For/Next loop?
0
 

Author Comment

by:edrz01
ID: 38383523
It would seem to be much faster with the ClearContents...
0
 
LVL 10

Expert Comment

by:etech0
ID: 38383562
By all means!

Unless you get an error, in which case you should probably switch it back...
0
 
LVL 10

Expert Comment

by:etech0
ID: 38383565
You can also try locking your header rows (1 and 2) in columns C and E, and see if it still works.
0
 

Author Comment

by:edrz01
ID: 38383594
etech0... I'm going to using the For/Next loop... though the other straight ClearContects a the endif of the test line... it affects the dropdown NMC. As in the back code from updating the time/stamp:
Private Sub Worksheet_Change(ByVal Target As Range)
    'Application.EnableEvents = True
    If Target.Value = "NMC" Then
        Target.Cells(1, 2).Value = Now()
    ElseIf Target.Value = "FMC" Or Target.Value = "PMC" Then
        Target.Cells(1, 2).Value = "" 'Start Time
        Target.Cells(1, 4).Value = "" 'Comments
    End If
End Sub

Where as if I use the For/Next loop, the Worksheet_Change still runs... oh well... I'm going to stay one step ahead.

I am so thankful for all you've done to resolve this problem I've had. I will close with you receiving the majority of the points. Now to figure that one out...lol...
0
 
LVL 10

Expert Comment

by:etech0
ID: 38383640
Glad to have been of help!

If you still have another problem after you close this question you can always start a new one :)

Have a great day!
0
 

Accepted Solution

by:
edrz01 earned 0 total points
ID: 38383644
nutsch - your initial code was the most direct to evaluate and replace the Status:
Application.EnableEvents = False
    '''' Change Status to FMC
    Range("B:B").Replace "NMC", "FMC"
    Range("B:B").Replace "PMC", "FMC"
>>>>>>>
   Application.EnableEvents = True

etech0 - your coding was the key to avert a major headache and achieve the process that I needed to complete this project:
For Each rgCell In Range(Cells(3, 3), Cells(Rows.Count, 3).End(xlUp))
    If rgCell.Locked = False Then rgCell.ClearContents
Next
    '''' Clear Comments
For Each rgCell In Range(Cells(3, 5), Cells(Rows.Count, 5).End(xlUp))
    If rgCell.Locked = False Then rgCell.ClearContents
Next

And removing the .SpecialCells fixed the Protection Error I received. Though the For/Next loop is a little slower...especially if there are thousands of records, it doesn't affect another procedure that requires a date/time stamp.

You will receive the majority of the points because of your dedicated help in resolving and sticking with me to fix the protection errors... Thank you again.!!! Kudo's to all that have such outstanding knowledge and expertise!
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38383653
You've actually given most of the points to me. If you want to change, you can click on the Request Attention link and reopen the question.

Thomas
0
 

Author Closing Comment

by:edrz01
ID: 38408818
I included my comment as a combined solution when putting etceh0 and nutsch codes together.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question