Hi
I am trying to find out the Header Row of ActiveSheet but somehow not able to succeed as few errors generated while trying the beautiful code adopted from
Will be highly appreciated if someone could correct me and helping to find the Activesheet or Activesheet.Name somewhere appropraitely activesheet needs to be defined
MsgBox findHeaderRow(Sheet4)------> this works perfectly as per the code below It gives you Header Row Number of Sheet 4
but when tried with below 2 syntaxes got the following errors
MsgBox findHeaderRow(ActiveSheet.Name) getting error Object required
MsgBox findHeaderRow(curSheetName) getting Error Byref argument type mismatch
Thanks
SamD
122
I am trying to find out the Header Row of ActiveSheet but somehow not able to succeed as few errors generated while trying the beautiful code adopted from
Will be highly appreciated if someone could correct me and helping to find the Activesheet or Activesheet.Name somewhere appropraitely activesheet needs to be defined
MsgBox findHeaderRow(Sheet4)------> this works perfectly as per the code below It gives you Header Row Number of Sheet 4
but when tried with below 2 syntaxes got the following errors
MsgBox findHeaderRow(ActiveSheet.Name) getting error Object required
MsgBox findHeaderRow(curSheetName) getting Error Byref argument type mismatch
VBA Code:
Private Sub cmdGetHdrRowNo_Click()
Dim wks As Excel.Worksheet
Dim strSheetName As String
'Dim curSheetName As String
curSheetName = ActiveSheet.Name
'Set wks = Application.ActiveSheet
'curSheetName = ActiveSheet.Name
'curSheetName = wks.Name[/I][/B]
MsgBox findHeaderRow(Sheet4)------> this works perfectly as per the code below It gives you Header Row Number of Sheet 4'
''MsgBox findHeaderRow(ActiveSheet.Name) getting error Object requred
''MsgBox findHeaderRow(curSheetName) getting Error Byref argument type mismatch
End Sub
Public Function findHeaderRow(wks As Excel.Worksheet) As Long
Const METHOD_NAME As String = "findHeaderRow"
Dim lastColumn As Long
If Not isSheetValid(wks) Then GoTo IllegalSheetException
lastColumn = lastNonEmptyColumn(wks)
findHeaderRow = firstNonEmptyRow(wks:=wks, startCol:=lastColumn, endCol:=lastColumn)
ExitPoint:
Exit Function
IllegalSheetException:
GoTo ExitPoint
End Function
Public Function lastNonEmptyColumn(wks As Excel.Worksheet, _
Optional startRow As Long, Optional startCol As Long, _
Optional endRow As Long, Optional endCol As Long, _
Optional ignoreHiddenCells As Boolean = False) As Long
Const METHOD_NAME As String = "lastNonEmptyColumn"
Dim lngCol As Long
Dim lngColStart As Long
Dim lngColEnd As Long
Dim lngNonBlanks As Long
Dim lngRowStart As Long
Dim lngRowEnd As Long
Dim rng As Excel.Range
If Not isSheetValid(wks) Then GoTo IllegalSheetException
If startCol > 0 And startCol <= wks.Columns.Count Then lngColStart = startCol Else lngColStart = 1
If endCol > 0 And endCol <= wks.Columns.Count Then lngColEnd = endCol Else _
lngColEnd = wks.Columns.Count
If startRow > 0 And startRow <= wks.Rows.Count Then lngRowStart = startRow Else lngRowStart = 1
If endRow > 0 And endRow <= wks.Rows.Count Then lngRowEnd = endRow Else lngRowEnd = wks.Rows.Count
Retry:
lngCol = 1
Do
Set rng = wks.Range(wks.Cells(lngRowStart, lngCol), wks.Cells(lngRowEnd, lngColEnd))
lngNonBlanks = Excel.Application.WorksheetFunction.CountA(rng)
If lngNonBlanks Then
If lngCol = lngColEnd Then Exit Do
lngColStart = lngCol
lngCol = lngCol + ((lngColEnd - lngCol + 1) / 2)
Else
lngColEnd = lngCol - 1
lngCol = lngColStart
If lngColStart > lngColEnd Then
lngCol = 0
Exit Do
End If
End If
Loop
If lngCol Then
If ignoreHiddenCells And wks.Columns(lngCol).Hidden Then
lngColEnd = nextVisibleColumn(wks, lngCol, Excel.xlToLeft)
If lngColEnd Then GoTo Retry
Else
lastNonEmptyColumn = lngCol
End If
End If
ExitPoint:
Exit Function
IllegalSheetException:
GoTo ExitPoint
End Function
Public Function isSheetValid(wks As Excel.Worksheet) As Boolean
Const METHOD_NAME As String = "isSheetValid"
Dim strSheetName As String
On Error Resume Next
strSheetName = wks.Name
isSheetValid = VBA.Len(strSheetName)
End Function
Public Function nextVisibleColumn(wks As Excel.Worksheet, initialCol As Long, _
direction As XlDirection) As Long
Const METHOD_NAME As String = "nextVisibleColumn"
Dim intOffset As Integer
Set wks = Application.ActiveSheet 'added by Sam
If Not isSheetValid(wks) Then GoTo IllegalSheetException
Select Case direction
Case Excel.xlToLeft: intOffset = -1
Case Excel.xlToRight: intOffset = 1
Case Else
nextVisibleColumn = initialCol
GoTo ExitPoint
End Select
nextVisibleColumn = initialCol
Do
nextVisibleColumn = nextVisibleColumn + intOffset
If Not wks.Columns(nextVisibleColumn).Hidden Then Exit Do
Loop
ExitPoint:
Exit Function
IllegalSheetException:
GoTo ExitPoint
End Function
Public Function firstNonEmptyRow(wks As Excel.Worksheet, _
Optional startRow As Long, Optional startCol As Long, _
Optional endRow As Long, Optional endCol As Long, _
Optional ignoreHiddenCells As Boolean = False) As Long
Const METHOD_NAME As String = "firstNonEmptyRow"
Dim lngRow As Long
Dim lngRowStart As Long
Dim lngRowEnd As Long
Dim lngNonBlanks As Long
Dim lngColStart As Long
Dim lngColEnd As Long
Dim rng As Excel.Range
If Not isSheetValid(wks) Then GoTo IllegalSheetException
If startCol > 0 And startCol <= wks.Columns.Count Then lngColStart = startCol Else lngColStart = 1
If endCol > 0 And endCol <= wks.Columns.Count Then lngColEnd = endCol Else _
lngColEnd = wks.Columns.Count
If startRow > 0 And startRow <= wks.Rows.Count Then lngRowStart = startRow Else lngRowStart = 1
If endRow > 0 And endRow <= wks.Rows.Count Then lngRowEnd = endRow Else lngRowEnd = wks.Rows.Count
Retry:
If endRow > 0 And endRow <= wks.Rows.Count Then
lngRow = endRow
Else
lngRow = wks.Rows.Count
End If
Do
Set rng = wks.Range(wks.Cells(lngRowStart, lngColStart), wks.Cells(lngRow, lngColEnd))
lngNonBlanks = Excel.Application.WorksheetFunction.CountA(rng)
If lngNonBlanks Then
If lngRow = lngRowStart Then Exit Do
lngRowEnd = lngRow
lngRow = lngRowStart + ((lngRow - lngRowStart - 1) / 2)
Else
lngRowStart = lngRow + 1
lngRow = lngRowEnd
If lngRowStart > lngRowEnd Then
lngRow = 0
Exit Do
End If
End If
Loop
If lngRow Then
If ignoreHiddenCells And wks.Rows(lngRow).Hidden Then
lngRowStart = nextVisibleRow(wks, lngRow, Excel.xlDown)
If lngRowStart <= wks.Rows.Count Then GoTo Retry
Else
firstNonEmptyRow = lngRow
End If
End If
ExitPoint:
Exit Function
IllegalSheetException:
GoTo ExitPoint
End Function
Public Function nextVisibleRow(wks As Excel.Worksheet, initialRow As Long, _
direction As XlDirection) As Long
Const METHOD_NAME As String = "nextVisibleRow"
Dim intOffset As Integer
If Not isSheetValid(wks) Then GoTo IllegalSheetException
Select Case direction
Case Excel.xlUp: intOffset = -1
Case Excel.xlDown: intOffset = 1
Case Else
nextVisibleRow = initialRow
GoTo ExitPoint
End Select
nextVisibleRow = initialRow
Do
nextVisibleRow = nextVisibleRow + intOffset
If Not wks.Rows(nextVisibleRow).Hidden Then Exit Do
Loop
ExitPoint:
Exit Function
IllegalSheetException:
GoTo ExitPoint
End FunctionSamD
122
?
