VB, How to find last row & delete inbetween

Closed
weenie - Jul 14, 2011 at 07:57 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 23, 2011 at 01:24 AM
Hello,

I have Headers in 1st row (A1 is empty). I need to find the last row (which can vary) then delete ALL rows inbetween last row and 2nd row? At end of it all I want to keep Header row, 2nd row and last row. Not sure how to go about writing code. Below is an example:


S1 S2 S3 S4 S5 S6 S7
0 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
21375.1 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
41688.5 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
64243.8 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
90895.3 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
124370 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
170083 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
237511 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
343918 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
519326 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
817277 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
1.33E+06 -28.432 -9.477 -29.657 -27.983 -27.205 -34.192 -31.9
2.01E+06 -27.501 -9.403 -28.646 -34.837 -26.472 -33.331 -30.886


Thanks
weenie

1 response

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 23, 2011 at 01:24 AM
try this
Public Sub doDelRows() 
   Dim lLastRow               As Long 
   Dim sSheet                 As String 
    
   sSheet = "Sheet1" 
   lLastRow = getItemLocation("*", Sheets(sSheet).Cells) 
   If (lLastRow > 3) _ 
   Then 
      Rows("3:" & lLastRow -1).Delete 
   End If 
    
End Sub 

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 

0