vba: select only cells that are not blank

[kylefoley76]

I'm going to run a formula in say column J and after I do that I'm going to sort them alphabetically then i want to only select the cells that are not blank using VBA, how do i do that?

 

[Jon von der Heyden]

Are the blanks nullstrings yielded by the formula? Or are they genuine blank cells (i.e. do not house formulas)?

What do you want to do with the selection afterwards?

 

[Teeroy]

Because the blanks are formula derived they're a bit tricky to pick up. The easiest way is to loop through the range and add non-blanks to build a Range. Something like:

Sub Build_Range_of_Non_Blanks()
Dim Rng as Range, Rng_Total As Range, Rng_Include As Range
Set Rng_Total = Application.Intersect(ActiveSheet.UsedRange, Columns("J"))
For Each Rng In Rng_Total
    If Rng.Value <> "" Then
        If Rng_Include Is Nothing Then
            Set Rng_Include = Rng
        Else
            Set Rng_Include = Application.Union(Rng_Include, Rng)
        End If
    End If
Next
End Sub

You could also easily turn this into a function if you need to use it more than once.

 

[kylefoley76]

actually i think it will be easier if we use an old technique that i picked up along the way. we run this formula

=IF(LEN(G1)-LEN(SUBSTITUTE(G1," ",""))=1,1,"#")

Then we find and replace the # with a blank. Then we copy and paste values, then we run this.

FindReplace "Sheet6", "#", "", "J"

Then i can sort from largest to smallest.

But then what I don't know how to do is possibly, move the selection to say j30 (because there will only be a few cells that are full), then if i hit cntrl up i should hit the first full cell.(but I can't do that in VBA) then if i hit cntrl up again that should select all the full cells. (but I can't do that step in vba either)

 

[kylefoley76]

anyone?

 

[Jon von der Heyden]

I think it's a bit soon to bump after 33 minutes. It knocks other members posts down the queue too quickly...

The reason I want to know what you want to do with the selection afterwards is to determine whether or not AutoFilter method may be useful here. Your approach seems rather long-winded to me.

 

[kylefoley76]

I'm just going to copy and paste it and move it to column B

 

[Jon von der Heyden]

Hi Kyle

Firstly there is no method called FindReplace - so that bit confuses me a bit.

Looking at post #4 and #7 I take it you want to copy values from one range to another, but exclude strings that contain space characters. Is that right? If so I recoomend an AutoFilter approach.

Say we have strings in column A, with labels in row1.

Excel 2010
	A
1	Name
2	Jon von der Heyden
3	JonvonderHeyden
4	Kyle Foley
5	KyleFoley
Sheet1

The goal is to populate column B with the names without spaces.

Sub Demo()
    Dim rngValues As Range
    Dim rngOutput As Range
    
    With Sheet1
        .AutoFilterMode = False
        Set rngValues = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        Set rngOutput = .Range("B2")
        .Range("B2:B" & Application.Max(2, .Cells(.Rows.Count, "B").End(xlUp).Row)).Clear
    End With
    With rngValues
        Call .AutoFilter(Field:=1, Criteria1:="<>* *")
        Call .Offset(1).SpecialCells(xlCellTypeVisible).Copy(Destination:=rngOutput)
        .AutoFilter
    End With
End Sub

Output will appear as follows:

Excel 2010
	A	B
1	Name	Output
2	Jon von der Heyden	JonvonderHeyden
3	JonvonderHeyden	KyleFoley
4	Kyle Foley
5	KyleFoley
Sheet1

 

[pgc01]

Hi Kyle

I think that one of the problems in this thread is that you are using the word "blank" that is a bad word in excel, because it can mean 2 different things. I'd stay away from it, or else make it clear to us what you mean.

The word blank is excel is bad because it can mean
- an empty cell
- an empty cell or a cell with a empty string

Depending on what is the case there can be different solutions. That's why I say it's better not to use it.
If you insist on using it, then make it clear which case it is, or else expect confusion.


Getting back to the problem, if your problem, like Jon says, is to copy only the values with no space to another column, try also:

Sub Test()
Dim v As Variant

v = Application.Transpose(Range("A2", Range("A" & Rows.Count).End(xlUp)))
v = Filter(v, " ", False)
Range("B2").Resize(UBound(v) + 1) = Application.Transpose(v)
End Sub

 

[Jon von der Heyden]

Sub Test()
Dim v As Variant

v = Application.Transpose(Range("A2", Range("A" & Rows.Count).End(xlUp)))
v = Filter(v, " ", False)
Range("B2").Resize(UBound(v) + 1) = Application.Transpose(v)
End Sub

Ooooh array filter to exclude spaces! Nice!