Results 1 to 7 of 7

Creating dynamic range

This is a discussion on Creating dynamic range within the Excel Questions forums, part of the Question Forums category; Hello, someone can recognize me with my questions, someone not. At start want to wish you good weekend. Heres my ...

  1. #1
    New Member
    Join Date
    Jul 2017
    Posts
    11

    Question Creating dynamic range

    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

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    7,501

    Default Re: Creating dynamic range

    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.


  3. #3
    New Member
    Join Date
    Jul 2017
    Posts
    11

    Default Re: Creating dynamic range

    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 by sh1ne; Jul 14th, 2017 at 08:47 AM.

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    7,501

    Default Re: Creating dynamic range

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


  5. #5
    New Member
    Join Date
    Jul 2017
    Posts
    11

    Default Re: Creating dynamic range

    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.

  6. #6
    Board Regular
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    863

    Default Re: Creating dynamic range

    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

  7. #7
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    7,501

    Default Re: Creating dynamic range

    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


Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com