Return a range of cells in column where ...
Return Range
Ignore header
Assumes no blank in that column.
Used a lot replacing an old buggy method in my recent project.
If you have to know, it is replacing ...
Set Rr = Range([c_Test_Pol], [c_Test_Pol].End(xlDown))
Function Column1Range(ColumnName, Optional StartingRow = 2, Optional Shee = "Active", Optional WB = "This") As Range
' Return a range of cells in column where ...
' Return Range
' Ignore header
' Assumes no blank in that column
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
Set Column1Range = Workbooks(WB).Worksheets(Shee).Range(ColumnName & StartingRow, ColumnName & WorksheetFunction.Counta(Workbooks(WB).Worksheets(Shee).Range(ColumnName & 1).EntireColumn))
' Set MyCol = ShP.Range("A2", "A" & WorksheetFunction.Counta(ShP.Range("A1").EntireColumn))
End Function
' Return a range of cells in column where ...
' Return Range
' Ignore header
' Assumes no blank in that column
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
Set Column1Range = Workbooks(WB).Worksheets(Shee).Range(ColumnName & StartingRow, ColumnName & WorksheetFunction.Counta(Workbooks(WB).Worksheets(Shee).Range(ColumnName & 1).EntireColumn))
' Set MyCol = ShP.Range("A2", "A" & WorksheetFunction.Counta(ShP.Range("A1").EntireColumn))
End Function
ColumnName, Optional StartingRow = 2, Optional Shee = "Active", Optional WB = "This"
Views 147
Downloads 41
CodeID
DB ID