Auto extend of range in excel macro
Closed
vermatarun22
Posts
1
Registration date
Wednesday September 19, 2012
Status
Member
Last seen
September 19, 2012
-
Sep 19, 2012 at 11:28 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 19, 2012 at 12:46 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 19, 2012 at 12:46 PM
Related:
- Auto extend of range in excel macro
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- How to stop auto refresh in facebook app - Guide
- Grand theft auto iv download apk for pc - Download - Action and adventure
- Spell number in excel without macro - Guide
- Auto redial in samsung - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Sep 19, 2012 at 12:46 PM
Sep 19, 2012 at 12:46 PM
you need to find out last row . for that you can use the formula
as example
lastrow = getitemlocation("*",cells)
newrange = range("A1:A" & lastrow )
as example
lastrow = getitemlocation("*",cells)
newrange = range("A1:A" & lastrow )
Public Function getItemLocation(sLookFor As String, _
rngSearch As Range, _
Optional bFullString As Boolean = True, _
Optional bLastOccurance As Boolean = True, _
Optional bFindRow As Boolean = True) As Long
'find the first/last row/column within a range for a specific string
Dim Cell As Range
Dim iLookAt As Integer
Dim iSearchDir As Integer
Dim iSearchOdr As Integer
If (bFullString) _
Then
iLookAt = xlWhole
Else
iLookAt = xlPart
End If
If (bLastOccurance) _
Then
iSearchDir = xlPrevious
Else
iSearchDir = xlNext
End If
If Not (bFindRow) _
Then
iSearchOdr = xlByColumns
Else
iSearchOdr = xlByRows
End If
With rngSearch
If (bLastOccurance) _
Then
Set Cell = .Find(sLookFor, .Cells(1, 1), xlValues, iLookAt, iSearchOdr, iSearchDir)
Else
Set Cell = .Find(sLookFor, .Cells(.Rows.Count, .Columns.Count), xlValues, iLookAt, iSearchOdr, iSearchDir)
End If
End With
If Cell Is Nothing Then
getItemLocation = 0
ElseIf Not (bFindRow) _
Then
getItemLocation = Cell.Column
Else
getItemLocation = Cell.Row
End If
Set Cell = Nothing
End Function
