How Can I Check Multiple Ranges?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks to this forum, I now know how to check a range with multiple cells to determine if it is blank. My next challenge is to check all the ranges in all of the worksheets in my workbook to ensure they are all blank before starting my procedure. I want to do this in Visual Basic but have no idea how to begin. I have about 15 range names I need to check. Should I be "looping" through relative or absolute sheetnames or range names ? Or should I be using Nested If Statements, or some third alternative? Any sample code would be greatly appreciated (as always).

Sincerely,

Fred
 
Fred

Therre is know 'right way' to do this. But if you can
refer to previously created named ranges in your code.

Sub ClearRange()
Application.Goto Reference:="MyBook.xls!MyRange"
Selection.ClearContents
End Sub

Sub ApplyColor()
Const limit As Integer = 25
For Each c In Range("MyRange")
If c.Value > limit Then
c.Interior.ColorIndex = 27
End If
Next c
End Sub

Using named ranges in the code means you do not have to
refer to the worksheet in the code. So if the ranges are
fixed use names but if they are dynamic and being added to
then use cell references.

nr = application.worksheetfunction.counta(range("A:A"))
rng = range(cells(1,1),cells(nr,6)
the rest of your code here

Regards
Peter
-----Original Message-----
Thanks to this forum, I now know how to check a range
Click to expand...
with multiple cells to determine if it is blank. My next
challenge is to check all the ranges in all of the
worksheets in my workbook to ensure they are all blank
before starting my procedure. I want to do this in Visual
Basic but have no idea how to begin. I have about 15
range names I need to check. Should I be "looping"
through relative or absolute sheetnames or range names ?
Or should I be using Nested If Statements, or some third
alternative? Any sample code would be greatly appreciated
(as always).
 
Back
Top