CODEWITHSUNDEEP
excelvbaautofilter
VBA_Anne_Marie
VBA_Anne_Marie

Reputation: 373

Find last column and row

Can someone please explain how to read the below code ? I undersatand that here we are looking for the last column and row, but it is not clear to me.

Set plage = .Range("a1:a" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)

and

plage.Offset(1).Resize(plage.Rows.Count - 1).EntireRow.SpecialCells(xlCellTypeVisible).Delete

Upvotes: 2

Views: 144

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

This

.Cells.Find("*", , , , xlByRows, xlPrevious).Row

uses the Range.Find method and looks into all cells .Cells of the entire worksheet and tries to find "*" which means find anything. xlByRows is the search order, it means search by row, and xlPrevious is the search direction means search backwards.

So it tries to find in all cells of the sheet row wise backwards starting from the end of the sheet anything that is not empty. This will end up in the last used cell of the sheet and .Row returns the row number of that cell.

Set plage = .Range("a1:a" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)

So plage is then the range starting from A1 until the last used row.

plage.Offset(1).Resize(plage.Rows.Count - 1).EntireRow.SpecialCells(xlCellTypeVisible).Delete

Then .Offset(1) will offset from that range by 1 row. If plage was A1:A10 after the offset it is A2:A11. Then .Resize(plage.Rows.Count - 1) will resize it by 1 row less than plage has rows making it A2:A10. The purpose of all this is to remove the header row from this range. Finally .EntireRow makes it the entire rows so rows 2 to 10 2:10 and .SpecialCells(xlCellTypeVisible) will take only the visible cells of that range and .Delete them.

The entire code looks a bit over complicate to me and you could reach the exact same actually easier.

Set plage = .Range("2:" & .Cells.Find("*", , , , xlByRows, xlPrevious).Row)

This will make plage directly 2:10 without having to use entire rows, offset and resize, so you just need to take the vibible cells from that and delete them.

plage.SpecialCells(xlCellTypeVisible).Delete

Note that SpecialCells(xlCellTypeVisible) might throw an error if there are no visible cells. So I recommend to do some error handling:

Dim CellsToDelete As Range
On Error Resume Next  ' hide all error messages because the next line might throw an error
Set CellsToDelete = plage.SpecialCells(xlCellTypeVisible)
On Error Goto 0  ' re-enable error reporting!

If Not CellsToDelete Is Nothing Then
    CellsToDelete.Delete
Else
    MsgBox "There are no visible cells to delete.", vbInformation
End If

Upvotes: 2

VBasic2008
VBasic2008

Reputation: 54807

Find and AutoFilter

  • Just addressing some minor issues.
Option Explicit

Sub FindAutoFilter()
    
    Dim plage As Range
    With ThisWorkbook.Worksheets("Sheet1")
        ' The 'Find' method may (will) fail if the worksheet is filtered
        '.AutoFilterMode = False
        Dim lCell As Range
        ' xlFormulas will allow hidden rows, xlValues may fail.
        Set lCell = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If lCell Is Nothing Then Exit Sub
        Set plage = .Range("A1:A" & lCell.Row)
    End With
    
    ' e.g.:
    plage.AutoFilter Field:=1, Criteria1:="Delete"
    ' The following makes sense only with the previous. Note the slight
    ' inaccuracy: in this case you should first resize, then offset.
    plage.Resize(plage.Rows.Count - 1).Offset(1) _
        .EntireRow.SpecialCells(xlCellTypeVisible).Delete

End Sub

Upvotes: 1

Related Questions