Formatting a Named Range

[DaveRadford]

Afternoon All,

I ahvea tricky one, i have data that i am sorting and then copying in to a field that is used as a named range. Im running the code below in order to change the data from TEXT ot numbers. I know that the data is AlphaNumeric but i would 3 digit numbers for Example "10" would be displayed as "010"

The issue that im having is that for some unknown reason the form that displays the information does contain all of the data in the output. If i checked the list within the named range its all there only when i click in to the named range i can see its only selecting half the data, and its never the same amount, but always misses some.

my named range formula is:

=OFFSET(WSList!$A$2:$A$301,0,0,COUNTIF(WSList!$A$2:$A$150,">"""),2)
I use this so that it will only display the data no blanks.

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True

Selection.NumberFormat = "000"
I'm a little confused as to why it would only select part of it, and to clarify the named range doesnt change it stays correct but i can see in the dotted lines that its decided to prematurely stop.

Help appreciated.

Many thanks,

Dave

 

[Andrew Poulsom]

Are the blanks all at the bottom? You won't get the right range if they are interspersed among the other cells. Why are you counting only 2:150 but offsetting 2:301?

 

[DaveRadford]

Sorry that was a typo, it is really 300. The blank cells are at the end.

I took a screenshot that explain it perfectly. How is best to post it? as the insert requires URL links.

Thanks,

Dave

 

[Andrew Poulsom]

Does this use the correct range?

=OFFSET(WSList!$A$2,0,0,COUNTA(WSList!$A$2:$A$301),2)

By the way, why 2 columns?

 

[DaveRadford]

Thanks, that works a treat, it was really confusing me. Do you happen to know that was happening?

I'm using the two columns as im returning the data in a form with two columns i may be filtering the one but i can pull both the code and the description in to the form using just the one named range.

Thanks,

Dave

 

[Andrew Poulsom]

I imagine that:

COUNTIF(WSList!$A$2:$A$301,">""")

wasn't returning what you expected.