Creating dynamic range

sh1ne

New Member
Joined
Jul 3, 2017
Messages
33
Office Version
  1. 2016
Platform
  1. Windows
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
dqKSmCU.png


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


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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
Assuming you filter the data to show only “NOT OK”.
Try this:
Code:
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
 
Upvote 0
Adding to Akuini's suggestion, if you want it to filter the sheet first, then copy results:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top