Creating dynamic range

[sh1ne]

Hello, someone can recognize me with my questions, someone not. At start want to wish you good weekend.

Heres my question:
How to create dynamic range using variables? Macro should copy 20 rows from bottom to another sheet, but there are some complications:

Below, on the image we see that macro should copy these rows correctly because we have more than 20 rows

But how to solve problem if there is not even 20 rows to copy:

As you see, the criteria of these rows is status "NOT OK", ofcourse I can use filters, so using variable f.e. lastRow should get me last 20 rows if its more than 20+. But what if there is not even 20 rows to copy?

If I understand, we should declare 2 variables to help us: lastRow and firstRow(first row after headers). All should be done within IF loop?

Someone can help me with code of this IF if I correctly understand that its neccessary to create dynamic range using IF loop or there is better solution?

Thanks

 

[JackDanIce]

Define your last row and use Range("A2:G" & LR).SpecialCells(xlCellTypeVisible) (Note A2, I'm assuming A1 contains your header which you do not want to copy)

You can avoid using loops, lots of examples of the SpecialCells property of the cell object if you search online but you should be able to adapt to your need.

 

[sh1ne]

But why you using SpecialCells? I wanted to use it because there are somewhere hidden columns that I dont want to copy 'em. Let me describe it once again:

We need to copy bottom 20 rows, lastRow will be always different. Ok, now we describe range by something like f.e. "Range(.Cells(lastRow, 10), .Cells(lastRow -20, 13)", because criterial is always status "NOT OK"(here its not important because I cna use filters to show only NOT OK rows). But what if there are only 14 rows with NOT OK? I need to describe range that it will be copying last 20 ROWS if there is more rows or if less than 20 it ends selecting/copying at firstRow co-ordinates (for example declared variable).

Sorry for my eng if its chaotic and incomprehensible.

 

[JackDanIce]

Specialcells xlcelltypevisible will only copy the visible cells: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-specialcells-method-excel

 

[sh1ne]

Yeah I know mate, but the problem is not about copying visible cells, I need to create dynamic range which allows me copy last 20 rows or by requirements(thats thing what Im asking for) from lastRow-to-firstRow.

 

[Akuini]

Assuming you filter the data to show only “NOT OK”.
Try this:

Sub a1014264a()
'https://www.mrexcel.com/forum/excel-questions/1014264-creating-dynamic-range.html
Dim r As Range, rng As Range
ra = Range("G:G").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
Set r = Range(Cells(2, "A"), Cells(ra, "A")).SpecialCells(xlCellTypeVisible)

If r.Cells.count >= 20 Then
    Set rng = Range(Cells(ra - 19, "A"), Cells(ra, "G")).SpecialCells(xlCellTypeVisible)
    Else
    Set rng = Range(Cells(2, "A"), Cells(ra, "G")).SpecialCells(xlCellTypeVisible)
    End If
End Sub

 

[JackDanIce]

Adding to Akuini's suggestion, if you want it to filter the sheet first, then copy results:

Sub m1()
    
    Dim x   As Long
    Dim LC  As Long
    
    With ActiveSheet
        If .AutoFilterMode Then .AutoFilterMode = False
        LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        x = Application.Max(.Cells(.Rows.Count, 7).End(xlUp).row - 19, 2)
        With .Cells(1, 1).Resize(x, LC)
            .AutoFilter
            .AutoFilter field:=7, Criteria1:="NOT OK"
            .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).Copy
        End With
    End With
    
End Sub