|
Thursday, June 11, 2009 - 7:07 PM
Public Function LastCol&(WSName$, Optional RowNum& = 1)
'returns 0 if rownum is invalid or row is empty (rownum defaults to 1 if argument is missing)
'rownum is optional bc all columns will have same last row if (used range of) ws is full of data
'uses TestLen function (see Basic Functions post in Functions category)
Dim numRows: numRows = Sheets(1).Rows.Count
Dim numCols: numCols = Sheets(1).Columns.Count
If (RowNum < 1&) Or (RowNum > Sheets(1).Rows.Count) Then Exit Function
With Sheets(WSName)
LastCol = Cells(numCols, RowNum).End(xlLeft).Column
If TestLen(.Cells(RowNum, Sheets(1).Columns.Count)) Then LastCol = numCols
If (LastCol = 1&) And Not TestLen(Cells(1, RowNum)) Then LastCol = 0
End With
End Function
Public Function LastRow&(WSName$, Optional ColNum& = 1)
'returns 0 if ColNum is invalid or column is empty (colnum defaults to 1 if argument is missing)
'ColNum is optional bc all columns will have same last row if (used range of) ws is full of data
'uses TestLen function (see Basic Functions post in Functions category)
Dim numRows: numRows = Sheets(1).Rows.Count
Dim numCols: numCols = Sheets(1).Columns.Count
If (ColNum < 1&) Or (ColNum > numCols) Then Exit Function
With Sheets(WSName)
LastRow = Cells(numRows, ColNum).End(xlUp).Row
If TestLen(.Cells(Sheets(1).Rows.Count, ColNum)) Then LastRow = numRows
If (LastRow = 1) And Not TestLen(Cells(1, ColNum)) Then LastRow = 0
End With
End Function
Public Function LastColWFormula&(WSName$, Optional RowNum& = 1)
'returns row number of last cell with a formula in a column
'returns 0 if ColNum is invalid or column is empty (rownum defaults to 1 if argument missing)
'rownum is optional bc all columns will have same last row if (used range of) ws is full of data
Dim numRows: numRows = Sheets(1).Rows.Count
Dim numCols: numCols = Sheets(1).Columns.Count
If (RowNum < 1&) Or (RowNum > numRows) Then Exit Function
With Sheets(WSName)
If (.Rows(RowNum).HasFormula = False) Then Exit Function 'will typically be Null, not True
LastColWFormula = Cells(RowNum, numCols).End(xlLeft).Column
If CBool(.Cells(RowNum, numRows).HasFormula) Then LastColWFormula = numRows
If (LastColWFormula = 1) And Not TestLen(Cells(1, ColNum)) Then LastColWFormula = 0: Exit Function
If Left(.Cells(LastRow, ColNum).Formula <> "=") Then
Dim i&
For i = LastColWFormula - 1 To 1 Step -1
If .Cells(i, ColNum).HasFormula Then Exit For
Next
LastColWFormula = i
End If
End With
End Function
Public Function LastRowWFormula&(WSName$, Optional ColNum& = 1)
'returns row number of last cell with a formula in a column
'returns 0 if colnum is invalid or column is empty (colnum defaults to 1 if argument missing)
'colnum is optional bc all columns will have same last row if (used range of) ws is full of data
Dim numRows: numRows = Sheets(1).Rows.Count
Dim numCols: numCols = Sheets(1).Columns.Count
If (ColNum < 1&) Or (ColNum > numCols) Then Exit Function
With Sheets(WSName)
If (.Columns(ColNum).HasFormula = False) Then Exit Function 'will typically be Null, not True
LastRowWFormula = Cells(numRows, ColNum).End(xlUp).Row
If CBool(.Cells(numCols, ColNum).HasFormula) Then LastRowWFormula = numCols
If (LastRowWFormula = 1) And Not TestLen(Cells(1, ColNum)) Then LastRowWFormula = 0: Exit Function
If Left(.Cells(LastRow, ColNum).Formula <> "=") Then
Dim i&
For i = LastRowWFormula - 1 To 1 Step -1
If .Cells(i, ColNum).HasFormula Then Exit For
Next
LastRowWFormula = i
End If
End With
End Function
|
|
| << Navigate to Thursday, June 11, 2009 |
Add New Comment |