Create a Range variable of only hardcoded numbers excluding dates

[Chris Macro]

I am trying to think of a way to develop a range variable that uses SpecialCells to narrow down the selected cells to only constant numbers and then narrows it down ever further to exclude dates. Any ideas?

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> NarrowRange()<br><br><SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> mySelection <SPAN style="color:#00007F">As</SPAN> Range<br><br>mySelection = Range("A1:C10")<br><br><SPAN style="color:#007F00">'Get only Hardcoded numbers</SPAN><br>  <SPAN style="color:#00007F">Set</SPAN> rng = mySelection.SpecialCells(xlCellTypeConstants, xlNumbers)<br><br><SPAN style="color:#007F00">'Remove cells with dates</SPAN><br>  <SPAN style="color:#007F00">'??????</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[mole999]

a date is just a plain old number that's formatted, i'm intrested to see if there is an answer

 

[Chris Macro]

Here's what I came up with. Not sure if it's the best way, but it appears to get the job done. I'm interested to see if there is a more efficient way!

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> NarrowRange()<br><br><SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> mySelection <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> rngDates <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> tempRange <SPAN style="color:#00007F">As</SPAN> Range<br><br><SPAN style="color:#00007F">Set</SPAN> mySelection = Range("A1:C10")<br><br><SPAN style="color:#007F00">'Get only Hardcoded numbers</SPAN><br>  <SPAN style="color:#00007F">Set</SPAN> rng = mySelection.SpecialCells(xlCellTypeConstants, xlNumbers)<br> <br> <SPAN style="color:#007F00">'Remove Any Date Values from HardCode Range</SPAN><br>  <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#007F00">'Create Date range</SPAN><br>      <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> rng.Cells<br>        <SPAN style="color:#00007F">If</SPAN> IsDate(cell) = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>          <SPAN style="color:#00007F">If</SPAN> rngDates <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rngDates = cell<br>          <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rngDates = Union(rngDates, cell)<br>          <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>      <SPAN style="color:#00007F">Next</SPAN> cell<br>      <br>    <SPAN style="color:#007F00">'Remove Date Cells from Hard Range</SPAN><br>      <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngDates <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> rng.Cells<br>          <SPAN style="color:#00007F">If</SPAN> Intersect(cell, rngDates) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> tempRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>              <SPAN style="color:#00007F">Set</SPAN> tempRange = cell<br>            <SPAN style="color:#00007F">Else</SPAN><br>              <SPAN style="color:#00007F">Set</SPAN> tempRange = Union(tempRange, cell)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>          <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cell<br>        <br>        <SPAN style="color:#00007F">Set</SPAN> rng = tempRange<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br>MsgBox "Range of only numbers: " & rng.Address<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>