0

The cell is going to change the value and it is in different locations of the worksheet. The point is that I haven't been able that my macro understand that criteria that is located in the last cell of a column, the second in this case

This is the code that I am using

Public Sub Excel()

    Dim T#, crit As Range, Data As Range, ws As Worksheet
    Dim r&, fc As Range, lc As Range, fr1 As Range, fr2 As Range

    Dim year%
    year = 2019

        Set fc = ActiveSheet.UsedRange.Item(1)
        Set lc = GetMaxCell
        Set Data = ActiveSheet.Range(fc, lc)
        Set ws = Sheets.Add
        With Data
            Set fr1 = Data.Worksheet.Range(fc, fc.Offset(, lc.Column))
            Set fr2 = ws.Range(ws.Cells(fc.Row, fc.Column), ws.Cells(fc.Row, lc.Column))
            With fr2
                fr1.Copy
                .PasteSpecial xlPasteColumnWidths: .PasteSpecial xlPasteAll
                .Item(1).Select
            End With
            Set crit = .Resize(2, 1).Offset(, lc.Column + 1)
            'This is the part that I need to understand
            crit = [{"AÑO";<>lRow)}]
            .AdvancedFilter xlFilterCopy, crit, fr2
            .Worksheet.Delete
        End With

End Sub

Public Function GetMaxCell(Optional ByRef rng As Range = Nothing) As Range

    'Returns the last cell containing a value, or A1 if Worksheet is empty

    Const NONEMPTY As String = "*"
    Dim lRow As Range, lCol As Range

    If rng Is Nothing Then Set rng = Application.ActiveWorkbook.ActiveSheet.UsedRange
    If WorksheetFunction.CountA(rng) = 0 Then
        Set GetMaxCell = rng.Parent.Cells(1, 1)
    Else
        With rng
            Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
                                        After:=.Cells(1, 1), _
                                        SearchDirection:=xlPrevious, _
                                        SearchOrder:=xlByRows)

            If Not lRow Is Nothing Then
                Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
                                            After:=.Cells(1, 1), _
                                            SearchDirection:=xlPrevious, _
                                            SearchOrder:=xlByColumns)

                Set GetMaxCell = .Parent.Cells(lRow.Row, lCol.Column)
            End If
        End With
    End If

End Function

Thanks for your help

Cesar_Torres
  • 3
  • 4
  • Your function returns a range so I think you need the `Row` property of that range. (But I'm not 100% clear.) – SJR Nov 15 '19 at 16:43
  • @SJR Yes, you are right. But the problem is that I can't make, Excel recognizes lrow as that value and the macro takes lrow as a character even with quotations, I don't know how to reach that – Cesar_Torres Nov 15 '19 at 17:26
  • Does that AF criteria syntax work if you remove the second bit? – SJR Nov 15 '19 at 17:42
  • @SJR No, because the first criteria is the column and the second is what i am goin to filter – Cesar_Torres Nov 15 '19 at 18:55
  • What I meant is, does it work if you hard code a number? I'm not sure if that syntax will work. – SJR Nov 15 '19 at 19:43
  • @SJR Yes, it is the best method that I found for deleting a big amount of rows. This is the source of the code https://stackoverflow.com/questions/30959315/excel-vba-performance-1-million-rows-delete-rows-containing-a-value-in-less – Cesar_Torres Nov 15 '19 at 19:58

0 Answers0