RandBetween range, skip cell without value in VBA

[Soraka]

I have this function that will go through BG2 and :BG17 and randomly display the value of a cell within that range in AS25.
But, sometimes some of these cells within the range have no value, and it displays nothing in AS25.
How can I prevent that, by skipping the cells without value?

Sub RandomFromRange()
Dim ws As Worksheet
Set ws = Worksheets("Main")
Application.ScreenUpdating = False
ws.Range("AS25") = WorksheetFunction.Index(Range("BG2:BG17"), WorksheetFunction.RandBetween(1, ws.Range("BG2:BG17").Rows.Count), WorksheetFunction.RandBetween(1, ws.Range("BG2:BG17").Columns.Count))
Range("AY1").Select
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

Try this

Sub RandomFromRange()
  Dim ws As Worksheet
  Dim b As Variant
  Dim c As Range, rng As Range
  Dim n As Long, i As Long
  
  Set ws = Worksheets("Main")
  Set rng = ws.Range("BG2:BG17")
  
  n = WorksheetFunction.CountA(rng)
  ReDim b(1 To n, 1 To 1)
  
  For Each c In rng
    If c.Value <> "" Then
      i = i + 1
      b(i, 1) = c.Value
    End If
  Next
  
  ws.Range("AS25") = b(WorksheetFunction.RandBetween(1, n), 1)
End Sub

 

[Soraka]

No, it didn't change anything from my function, it still displays the cells without value.

 

[DanteAmor]

No, it didn't change anything from my function, it still displays the cells without value.

You must replace your code with the macro that I put in post #2

 

[Peter_SSs]

Firstly, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy.
My signature block below has more details.

I'm guessing that BG2:BG17 is populated with formulas meaning that all the cells actually have a value, some of which may be ""?
Give this code a try.

Sub RandomFromRange_v2()
  Dim ws As Worksheet
  Dim a As Variant
 
  Set ws = Worksheets("Main")
  Randomize
  With ws.Range("BG2:BG17")
    a = .Worksheet.Evaluate(Replace("filter(#,#<>"""")", "#", .Address))
  End With
  ws.Range("AS25").Value = a(1 + Int(Rnd() * UBound(a)), 1)
End Sub

 

[Soraka]

Firstly, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy.
My signature block below has more details.

I'm guessing that BG2:BG17 is populated with formulas meaning that all the cells actually have a value, some of which may be ""?
Give this code a try.

Sub RandomFromRange_v2()
  Dim ws As Worksheet
  Dim a As Variant
 
  Set ws = Worksheets("Main")
  Randomize
  With ws.Range("BG2:BG17")
    a = .Worksheet.Evaluate(Replace("filter(#,#<>"""")", "#", .Address))
  End With
  ws.Range("AS25").Value = a(1 + Int(Rnd() * UBound(a)), 1)
End Sub

I always forget, I rarely post here, sorry.
I tried the code you provided, but it comes up with an error, Type mismatch, on the line at the end:

ws.Range("AS25").value = a(1 + Int(Rnd() * UBound(a)), 1)

@DanteAmor
I did replace my code wwith yours, but it doesn't seem to change anything from my own code.
The reason being, if I calculate (I click a button to refresh the sheet, it will roll randomly in that function), it will from time to time show in AS25 the content of empty cells within BG2:BG17.
I click that refresh button, it will show the content of non empty cell, then again I hit the refresh button, this time it displays nothing because it rolled an empty cell within BG2;BG17.

The content of those cells is simply the result of a formula, which is:

=IF(AZ2=TRUE,VLOOKUP(AS2,Names!C1:H1000,6,0),"")

If TRUE, then it will show a number.
So, AS25 either shows a number that is retrieved in those cells BG2:BG17, or empty cell (which is what I want to avoid).

 

[DanteAmor]

The content of those cells is simply the result of a formula

That changes things, the macro requires a small change in the way you count blank cells.

Try the following:

Sub New_Macro_RandomNumber()
  Dim ws As Worksheet
  Dim b As Variant
  Dim c As Range, rng As Range
  Dim n As Long, i As Long
  
  Set ws = Worksheets("Main")
  Set rng = ws.Range("BG2:BG17")
  
  n = rng.Rows.Count - WorksheetFunction.CountBlank(rng)
  ReDim b(1 To n, 1 To 1)
  
  For Each c In rng
    If c.Value <> "" Then
      i = i + 1
      b(i, 1) = c.Value
    End If
  Next
  
  ws.Range("AS25") = b(WorksheetFunction.RandBetween(1, n), 1)
End Sub

 

[Peter_SSs]

I tried the code you provided, but it comes up with an error, Type mismatch, on the line at the end:

That error should only occur if every cell in BG2:BG17 = ""
However, my code would also error (different error) if only one cell contained a number.
This should avoid both errors.

Sub RandomFromRange_v3()
  Dim ws As Worksheet
  Dim a As Variant
  
  Set ws = Worksheets("Main")
  Randomize
  With ws.Range("BG2:BG17")
    a = .Worksheet.Evaluate(Replace("filter(#,#<>"""")", "#", .Address))
  End With
  If Not IsError(a) Then
    If UBound(a) = 1 Then
      ws.Range("AS25").Value = a(1)
    Else
      ws.Range("AS25").Value = a(1 + Int(Rnd() * UBound(a)), 1)
    End If
  End If
End Sub