function that finds length of a table; Cannot run on a different sheet.

Hi guys,

The BlanksToSkip() code I use all the time and want to modify it, so it can work within the sub Sample().
I have highlighted where it trips up. In the past I have had to use "Active.worksheet" before calling BlanksToSkip(). I would prefer to have a way for BlanksToSkip to work regardless, or maybe another parameter as the worksheet? whatever is best.

Thanks!
Jimmy

Sub Sample()
Dim str02 As String, str03 As String, str04 As String
Dim wsCAF As Worksheet, swDocNumReg As Worksheet
Dim Rng01 As Range, Rng02 As Range, Rng03 As Range, Rng04 As Range
Dim cell As Range
'''''''''Dimensions
'''''''''Variables
str02 = "Commitment Authority Form Reg"
str03 = "Document NumberingExample"
'''''''''Variables

Set wsCAF = Worksheets(str02)
Set swDocNumReg = Worksheets(str03)

'Activate.wsCAF

Set Rng01 = wsCAF.Range("A4")
Set Rng03 = swDocNumReg.Range("A2")
Call Functions_Module.BlankstoSkip(Rng01, "d", Rng02, 4)
Call Functions_Module.BlankstoSkip(Rng03, "d", Rng04, 4)

For Each cell In Range(Rng01, Rng02)
Next cell
End sub

'Is within a module called "Functions_Module"
Function BlankstoSkip(X1 As Range, Direction As String, Optional X2 As Range, Optional Blanks As Long)
'Call Functions_Module.BlanksToSkip(x1, "Right",x2, 1)
'x1 is the start of the search, x2 what will be saved as the result. Optional as you may not want to save x2
'Direction must be "up", "down", "left", "right" or "U", "D", "L", "R"
'If 'Blanks' left out, then assumes zero blanks within data column/row.
'
'BlanksToSkip finishes the program with the selected cell being x2.
'
'Breaks if going up and the has a search attached to row 1.
'Breaks if going left and the search is attached to column1.

Dim aaa As String
aaa = ""
Set X2 = X1
'X2.Select 'trips up here, when running "Call Functions_Module.BlankstoSkip(Rng01, "d", Rng02, 4)"
If UCase(Direction) = "UP" Or UCase(Direction) = "U" Then: x = 0: y = -1: aaa = xlUp
If UCase(Direction) = "DOWN" Or UCase(Direction) = "D" Then: x = 0: y = 1: aaa = xlDown
If UCase(Direction) = "LEFT" Or UCase(Direction) = "L" Then: x = -1: y = 0: aaa = xlToLeft
If UCase(Direction) = "RIGHT" Or UCase(Direction) = "R" Then: x = 1: y = 0: aaa = xlToRight
If aaa = "" Or Blank < 0 Then MsgBox ("Error within 'Direction' or Blanks are negative of function BlanksToSkip"): Exit Function

If x = 0 Then
For i = 1 To Blanks + 1
Do While Not IsEmpty(X2.Offset(i * y, ii * x).Value)
CallByName(Selection, "End", VbGet, aaa).Select
Set X2 = Selection
i = 1
ii = 1
Loop
Next i
End If

If y = 0 Then
For ii = 1 To Blanks + 1
Do While Not IsEmpty(X2.Offset(i * y, ii * x))
CallByName(Selection, "End", VbGet, aaa).Select
Set X2 = Selection
i = 1
ii = 1
Loop
Next ii
End If

End Function

It seems to work when I comment out the 'X2.Select. I think. thanks AlphaFrog. I still need to check something else though.

Like there is another problem that's linked to this, but I am going to continue it on the related thread:
https://www.excelforum.com/excel-pro...ml#post4807023


...
I just tired this again, and I had to UNCOMMENT out the 'X2.Select to get it to work..
So I problem is a bit of a weird one.