Hello everyone
Is there a way to check if a string is valid range or not?
Example: If I have string like that "A1:D10" .. How can I check if this string is a valid range or not?
Check if string is valid range
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Check if string is valid range
For example:
Example of usage:
Result in the Immediate window:
Code: Select all
Function ValidRange(MyString As String) As Boolean
Dim MyRange As Range
On Error Resume Next
Set MyRange = Range(MyString)
ValidRange = (Err = 0)
End Function
Code: Select all
Sub Test()
Dim MyString As String
MyString = "A1:A10"
Debug.Print MyString, ValidRange(MyString)
MyString = "A1:A"
Debug.Print MyString, ValidRange(MyString)
End Sub
Code: Select all
A1:A10 True
A1:A False
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4931
- Joined: 31 Aug 2016, 09:02
Re: Check if string is valid range
Thank you very much. Amazing solution.
Here's my try that I need to evaluate that approach (to make sure it is right)
Here's my try that I need to evaluate that approach (to make sure it is right)
Code: Select all
Sub Test()
Dim e
e = "A1:A10"
If IsNumeric(Evaluate("SUM(" & e & ")")) And Not IsNumeric(e) Then
Debug.Print "Valid Range"
End If
End Sub
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: Check if string is valid range
Nice use of on error resume next Hans!!!
Lisa
Lisa
-
- Administrator
- Posts: 12628
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe