Named Range.clearcontents issue

[JST013]

Hello MR.EXCEL users,

Its been a little while, I have been smooth sailing let me tell you!

Ran into a little problem today... The following code is working perfectly, but is obviously cumbersome if the range gets too big.

Dim job As Range
For Each rng In ws.Names
      If InStr(1, rng.Name, "CAR", vbTextCompare) > 0 Then   
            Set job = ws.Range(rng.Name)

For x = 1 To job.Rows.Count
If job.Cells(x, g) = vbNullString Then GoTo 1
    For q = y To job.Columns.Count
        job.Cells(x, q) = vbNullString
    Next q
1   x = x + 1
Next x
end if
next rng

Note I am also looping through several ranges so it causes quite a bit of lag to do it this way so I tried the following.

job.Range(job.Cells(1, y), job.Cells(job.Rows.Count, job.Columns.Count)).ClearContents

And it is not working at all...

My range does include some cells that are hidden, but I am not sure that that is the problem as it does not seem to even start where I intended it to.

Thank you in advance for any help

 

[JoeMo]

Can you explain what exactly you want to do with your named ranges? Are all your named ranges scoped at worksheet level, or workbook level, or a mix of the two?

 

[JST013]

Hi JoeMO,

So all of the named ranges are worksheet scoped and I am trying to remove any values from the cells without removing formatting.

thank you!

 

[JoeMo]

Hi JoeMO,

So all of the named ranges are worksheet scoped and I am trying to remove any values from the cells without removing formatting.

thank you!

This will clear contents of each sheet-scoped, named range in the active sheet.

Sub JST013()
Dim nm As Name
For Each nm In ActiveSheet.Names
    Range(nm.Name).ClearContents
Next nm
End Sub

 

[JST013]

Sorry JoMo, I did not specify, but there are only certain parts of the range I would like to clear contents.


[TABLE="width: 300"]
<tbody>[TR]
[TD]Name[/TD]
[TD]May[/TD]
[TD]April[/TD]
[TD]June[/TD]
[TD]July[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jean[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Joan[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

In the above table I want to only clear the "X"'s. When I tried my code it would clear maybe from B3:E8.

However this might work if it doesn't effect protected cells. I will try it.

 

[JoeMo]

Sorry JoMo, I did not specify, but there are only certain parts of the range I would like to clear contents.


[TABLE="width: 300"]
<tbody>[TR]
[TD]Name[/TD]
[TD]May[/TD]
[TD]April[/TD]
[TD]June[/TD]
[TD]July[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jon[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jean[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Joan[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

In the above table I want to only clear the "X"'s. When I tried my code it would clear maybe from B3:E8.

However this might work if it doesn't effect protected cells. I will try it.

Try this:

Sub JST013()
Dim nm As Name
For Each nm In ActiveSheet.Names
    Range(nm.Name).Replace what:="X", replacement:=""
Next nm
End Sub

 

[JST013]

Thank you JoeMo that works perfectly, however I have found a case where this still does not quite fit my needs. Unfortunately the "X" values are not as predictable as I thought so I went with this:

Set job = Range(nm.Name).Cells.SpecialCells(xlCellTypeConstants, xlNumbers)

within the loop. This one works really well and is very fast, but again, another scenario popped up where the name may be a number instead of a string. In that case I lose the name.

Do you know if I can exclude the name column ("A" in this example) using this code or similar code, without reshaping my ranges?

If not I can just redo the ranges. I am quite satisfied with the code with the few exceptions to the rules

thank you very much for your help JoeMo.

 

[JoeMo]

Thank you JoeMo that works perfectly, however I have found a case where this still does not quite fit my needs. Unfortunately the "X" values are not as predictable as I thought so I went with this:

Set job = Range(nm.Name).Cells.SpecialCells(xlCellTypeConstants, xlNumbers)

within the loop. This one works really well and is very fast, but again, another scenario popped up where the name may be a number instead of a string. In that case I lose the name.

Do you know if I can exclude the name column ("A" in this example) using this code or similar code, without reshaping my ranges?

If not I can just redo the ranges. I am quite satisfied with the code with the few exceptions to the rules

thank you very much for your help JoeMo.

You are welcome.

If you have a named range (say "dog" for example), this will select all but the first column portion of that range:

Range("dog").Columns(1).offset(0,1).resize(,Range("dog").columns.count-1).select

 

[JST013]

Thank you so much JoMo. That is working beautifully.

rng = Range("dog").Columns(1).offset(0,1).resize(,Range("dog").columns.count-1).Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
rng.value = vbnullstring