Amending code question

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

If TargetColumn holds the value '7' and myRange is dimmed as
Range, how may I 'Set' myRange to get all populated cells in Col 7,
so that I can then do:

On Error Resume Next
myRange.SpecialCells(xlCellTypeConstants, xlNumbers) _
.ClearContents
myRange.SpecialCells(xlCellTypeFormulas).ClearContents
On Error GoTo 0

Regards.
 
Stuart,

Try something like the following:

Dim MyRange As Range
Dim TargetColumn As Integer
TargetColumn = 7
With ActiveSheet
Set MyRange = Application.Intersect(.UsedRange,
..Columns(TargetColumn))
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Many thanks.

Regards.

Chip Pearson said:
Stuart,

Try something like the following:

Dim MyRange As Range
Dim TargetColumn As Integer
TargetColumn = 7
With ActiveSheet
Set MyRange = Application.Intersect(.UsedRange,
.Columns(TargetColumn))
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
Click to expand...
 
Answered a little too soon!

Here's my attempt to integrate the answer into my code:

For Each ws In ActiveWorkbook.Worksheets
With ws
If Not (UCase(.Name) = "MASTER" Or UCase(.Name) _
= "COVER" Or UCase (.Name) = "CONTENTS" Or _
UCase(.Name) = "GENERAL SUMMARY") Then
.Unprotect
.Select

TargetColumn = Cells.Find(What:="ClientCost", _
After:=.Range("A1"), LookIn:=xlFormulas, lookat:=xlPart, _
searchorder:=xlByColumns, searchdirection:=xlNext, _
MatchCase:=False).Column

Set MyRange = Application.Intersect(.UsedRange, _
.Columns(TargetColumn))
On Error Resume Next
MyRange.SpecialCells(xlCellTypeConstants,
xlNumbers).ClearContents
MyRange.SpecialCells(xlCellTypeFormulas).ClearContents
On Error GoTo 0
End If
End With
Next

Why am I getting an Application-defined or object-defined error on the
Set line please?

Regards.
 
Stuart,

Are you sure that TargetColumn is getting set to a positive
number? If Find doesn't find the text, TargetColumn will get a
zero value, and that will cause a 1004 error on the Set
statement.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Found my error....had not Dimmed TargetColumn As Integer.
So even though Locals showed a value of "7" (the correct
column) because TargetColumn was Dimmed As String, it
could not be accepted as an acceptable 'value' for a column.
I think(?).

Thanks again for the help.

Regards.
 
Back
Top