Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clearing excess formatting in a workseheet

    I have written a mocro that clears all the exces formatting from rows at the bottom of a worksheet.

    Now I would like to extend the macro to also clear the excess formatting that appears on any columns on the RHS of the worksheet

    Please find below my current code.

    Can anyone please help with a neat bit of code that allows me too select the relevant columns to be cleared.

    Thanks

    Function ClearBottomOfASheet(ws As Worksheet)
    Dim i As Integer
    Dim lastRow As Long
    Dim LastCol As Long
    ws.Activate
    lastRow = LastCellInWS(ws).Row + 5
    LastCol = LastCellInWS(ws).Column + 5
    Rows(lastRow & ":" & lastRow).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Clear
    'THIS IS THE SEECTION I NEED HELP WITH
    'Columns(LastCol & ":" & LastCol).Select
    'Range(Selection, Selection.End(xlRight)).Select
    'Selection.Clear
    ws.Cells(3, 1).Activate
    End Function
    Function LastCellInWS(ws As Worksheet) As Range
    Dim lastRow As Long, LastCol As Long
    On Error Resume Next
    With ws
    lastRow = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    LastCol = .Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByColumns).Column
    End With
    If lastRow = 0 Then lastRow = 1
    If LastCol = 0 Then LastCol = 1
    Set LastCellInWS = ws.Cells(lastRow, LastCol)
    End Function

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Try this:
    Code:
    Function ClearBottomOfASheet(ws As Worksheet)
       Dim i                 As Long
       Dim lastRow           As Long
       Dim LastCol           As Long
    
    
       lastRow = LastCellInWS(ws).Row + 5
       LastCol = LastCellInWS(ws).Column + 5
    
    
       With ws
          .Range(.Cells(lastRow, 1), .Cells(.Rows.Count, 1)).EntireRow.Clear
          .Range(.Cells(1, LastCol), .Cells(1, .Columns.Count)).EntireColumn.Clear
       End With
    End Function
    Function LastCellInWS(ws As Worksheet) As Range
       Dim lastRow           As Long
       Dim LastCol           As Long
    
    
       lastRow = 1
       LastCol = 1
       
       On Error Resume Next
    
    
       With ws
          lastRow = .Cells.Find(What:="*", _
                                SearchDirection:=xlPrevious, _
                                SearchOrder:=xlByRows).Row
          LastCol = .Cells.Find(What:="*", _
                                SearchDirection:=xlPrevious, _
                                SearchOrder:=xlByColumns).Column
       End With
       Set LastCellInWS = ws.Cells(lastRow, LastCol)
    End Function
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •