PDA

View Full Version : Select Non Continuous Range



rompetelo
07-04-2016, 11:35 PM
Hello,

I am quite new at VBA ans I have an issue I was hoping someone could help me with. I am trying to select a non-contious (variable range). But never spaced with more than one blank row.

Basically the first cell is H6 and the last one as of today is K132. But tomorrow it could be K133 or 134. I think the code should be something with counting up until there is less than one blank row?

Does anyone know how to do this?

Thanks,

Rompetelo

snb
07-05-2016, 01:12 AM
In VBA you should always avoid 'Select' and 'Activate'.

rompetelo
07-05-2016, 01:33 AM
Ok.. Appreciate the feedback as I am new - any suggestions on how to solve my problem?

p45cal
07-05-2016, 03:27 AM
What's in the cells that are not blank? Formulas or alphanumeric?
Experiment recording yourself selecting entire columns H:K then clicking F5 on the keyboard and choosing Special..., and choosing Constants or Formulas and the combinations of checkboxes. If it's a mix of formulae and alphanumeric(constants, non-formulae, whatever) we can still help. Get the selection right doing this, post your code and we'll tweak to get rid of verbose code and selecting.

rompetelo
07-05-2016, 10:07 AM
Both formulas and alphanumeric for others
I tried recording your trick with the last cell but it doesnt work - I actually ended up setting up my spreadsheet in a different way as this must be impossible to do

p45cal
07-05-2016, 10:52 AM
Both formulas and alphanumeric for others
I tried recording your trick with the last cell but it doesnt work - I actually ended up setting up my spreadsheet in a different way as this must be impossible to do"doesn't work" is so unhelpful. Post what was recorded, even if it is only nearly right. Do it twice: once for constants, once for formulae.
Otherwise post a file.
It's very, very unlikely to be impossible.

jolivanes
07-05-2016, 11:47 AM
This will select all cells in the given range that have a value of more than 10.
But as mentioned previously, selecting is under normal circumstances not required and should not be used.



Sub Select_All_Cells_10_Plus()
Dim rCell As Range
Dim lVal As Long
Dim rngVal As Range
Dim rng As Range
Set rng = Range("B1:D" & Cells(Rows.Count, 2).End(xlUp).Row)
Set rngVal = Nothing
For Each rCell In rng
If rCell.Value >10 Then
If rngVal Is Nothing Then
Set rngVal = rCell
Else
Set rngVal = Union(rngVal, rCell)
End If
End If
Next
rngVal.Select
Set rCell = Nothing
End Sub


It just about sounds like you want to select every other row.
Your information lacks clarity though.

mdmackillop
07-05-2016, 01:19 PM
Sub Test()
Dim r As Range, rC As Range, rF As Range
On Error Resume Next
With Range("H:K")
Set rC = .SpecialCells(2)
Set rF = .SpecialCells(-4123)
End With
On Error GoTo 0
Set r = Union2(rC, rF)
r.Select
End Sub


Function Union2(ParamArray Ranges() As Variant) As Range
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Union2 http://www.cpearson.com/excel/BetterUnion.aspx
' A Union operation that accepts parameters that are Nothing.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim N As Long
Dim RR As Range
For N = LBound(Ranges) To UBound(Ranges)
If IsObject(Ranges(N)) Then
If Not Ranges(N) Is Nothing Then
If TypeOf Ranges(N) Is Excel.Range Then
If Not RR Is Nothing Then
Set RR = Application.Union(RR, Ranges(N))
Else
Set RR = Ranges(N)
End If
End If
End If
End If
Next N
Set Union2 = RR
End Function