Autofilter Copy Blank Row

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello all,


    I have searched through alot of OZGRID and even found the helpsites revolving around Autofilter, Autofilter-criteria, Autofilter-vba-criteria, Autofilter-vba... and I still could not find a 'stop' if there is no contents.


    Basically, I want to be able to Autofilter a specific criteria and copy over to another sheet without the header, then in the first column it'll change the information to a date, then copy back over to a third sheet. I have accomplished all of that.


    My problem lies with that if there is no contents when the Autofilter is applied, it will still continue to do the steps and then I have Run-time error '1004' AutoFill method of Range class failed. Because there is no contents it tries to do this.


    Any help would be greatly appreciated. I have been going through all the words in my head that maybe someone else had this problem but I could not locate anything that would fall right where I needed it.


    thank you,
    david

    Re: Autofilter Copy Blank Row


    Hi,


    Try


    [vba]Sub autofilter_copy_paste_feature()
    Dim a, w(), i As Long, j As Long, c As Long, ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("temp"): Set ws2 = Sheets("New Entry")


    With Sheets("Current")
    a = .[a1].CurrentRegion
    End With
    ReDim w(1 To UBound(a, 1), 1 To UBound(a, 2))
    For i = 2 To UBound(a, 1)
    If a(i, 1) = "New Entry" Then
    j = j + 1: w(j, 1) = Date:
    For c = 2 To UBound(a, 2): w(j, c) = a(i, c): Next
    End If
    Next
    With ws1.[a1]
    .CurrentRegion.Clear
    .Resize(j, UBound(a, 2)).Value = w
    End With
    With ws2
    .Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(j, UBound(a, 2)).Value = w
    End With
    End Sub[/vba]


    HTH

    Re: Autofilter Copy Blank Row


    Maybe I too should have gone down the complete rewrite road, however I came across an interesting nugget or two about Autofilter while researching the answer. Specifically that Excel creates a hidden named range in the workbook when AutoFilter is on. It's called 'Sheet1!_FilterDatabase', or in this case 'Current!_FilterDatabase'. I didn't use it in the end.


    See attached file. It seems to work for whether there are 0, 1 (your code had some problems with 1) or any number of 'New Entry' rows.


    p45cal

    Re: Autofilter Copy Blank Row


    1. Krishnakumar, I tried your code and I could not figure out if it did anything, I put it in and ran the code, so unless I was not suppose to put it in the module it did nothing for me. If possible, could you explain what it was suppose to do?


    2. :congrats:and thank you, p45cal, your attachment is exactly what I was looking for. It worked perfectly and without hesitation. I am going to add it to the rest of my coding and then I can move on to more and better things. Thank you... Thank you.


    note: you mentioned that you found 'Sheet1!_FilterDatabase', and 'Current!_FilterDatabase' , could you tell me where this was so that I might read it as well?


    3. And I thank all of the OZGRID's folks for having similar problems as mine, my experience sofar lies with the macro recorder and being able to piece things together. As I can see from the majority of the threads I have read that I need to be able to understand and create from from paper and pencil all the way to being able to code it myself with the continued help of OZGRID.


    thank you,
    David

    Thanks,
    David
    [size=-2]Bad news. These are the good old days.[/size]

    Re: Autofilter Copy Blank Row


    simply as an FYI to anyone and p45cal, but when I have the vbe window open and run from that window or if I wanted to watch it step by step by adding breakpoints, it would give the Run-time error'1004': Method 'Sheets' of object'_Global' failed


    It will run good as long as I don't do either of the two things there. So I am curious, would there be a problem with this in the long run? Or is there a work-around for this?


    once again, thank you,
    David

    Thanks,
    David
    [size=-2]Bad news. These are the good old days.[/size]

    Re: Autofilter Copy Blank Row


    Hi,
    My understanding was you filter the column for "new entry" on current sheet and paste the results in temp sheet(after clearing) as well as new entry sheet(in the end). If so just replace your code with mine. It goes in standard module.

    Re: Autofilter Copy Blank Row


    Krishnakumar, I thought maybe I would create a new book just to see what could be and it worked perfectly to. :congrats:


    I am not sure but I think my original problem why your code did not work the first time was maybe because of the sheet numbering? I am putting the codes into a larger workbook and the three sheets I have may be at ws15 or ws50. I don't know if your code will be able to work around that, but I did see that it is very fast and at first, I didn't think it did anything because it was already done before my eyes even had a chance to blink.


    thank you Krishnakumar for your codes, I am going to play with the two things that I have recieved from this thread. I know that I will be coming back to this thread as well as many others that the other folks have submitted.


    thank you,
    David

    Thanks,
    David
    [size=-2]Bad news. These are the good old days.[/size]

    Re: Autofilter Copy Blank Row


    I just placed it in one of my 7 codes and it worked without a problem.


    again thank you Krishnakumar and p45cal for taking the time to help me with my project.


    and if you must know, I did resort to using Krishnakumar codes, but I am definitely keeping p45cal handiwork as well.


    I need all the help I can get.


    thank you,
    david

    Thanks,
    David
    [size=-2]Bad news. These are the good old days.[/size]

    Re: Autofilter Copy Blank Row


    Krishnakumar, your code works as long as I definitely have a "New Entry"? Because sometimes there is no New Entry and I don't want it to bug out when there is nothing to transfer.


    The reason that I ask is because I have accomplished in putting it in my codes and have came up with the error along these parts.


    Run-time error'1004':
    Application-defined or object-defined error


    The same error code happens on both of these lines.

    Code
    .Resize(j, UBound(a, 2)).Value = w

    and again on

    Code
    .Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(j, UBound(a, 2)).Value = w


    your help or anyone else's is greatly appreciated.
    I know that this post came at a late time and I am leaving for the weekend in about five minutes. I will be back Monday morning.


    Have a good weekend!
    thank you,
    David

    Thanks,
    David
    [size=-2]Bad news. These are the good old days.[/size]

    Re: Autofilter Copy Blank Row


    OK. Try,


    [vba]Sub autofilter_copy_paste_feature()
    Dim a, w(), i As Long, j As Long, c As Long, ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("temp"): Set ws2 = Sheets("New Entry")

    With Sheets("Current")
    a = .[a1].CurrentRegion
    End With
    ReDim w(1 To UBound(a, 1), 1 To UBound(a, 2))
    For i = 2 To UBound(a, 1)
    If a(i, 1) = "New Entry" Then
    j = j + 1: w(j, 1) = Date:
    For c = 2 To UBound(a, 2): w(j, c) = a(i, c): Next
    End If
    Next
    If j > 0 Then
    With ws1.[a1]
    .CurrentRegion.Clear
    .Resize(j, UBound(a, 2)).Value = w
    End With
    With ws2
    .Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(j, UBound(a, 2)).Value = w
    End With
    End If
    End Sub[/vba]


    HTH

    Re: Autofilter Copy Blank Row


    This one does away with the requirement for the 'temp' sheet:



    P45cal

    Re: Autofilter Copy Blank Row


    Good Morning all, hope everyone had a fairly good weekend. Sorry I was not able to get back to this thread so soon, I use the computer at work for internet.


    I do want to thank p45cal and Krishnakumar for submitting revised coding for my project.
    I had decided to tryout p45cal's code after I saw this.

    Quote from p45cal

    This one does away with the requirement for the 'temp' sheet:

    and it had me convinced that I should try this one, and it worked without a problem either, since I am trying to avoid making the files any bigger than they have to be.



    I am still new to all this programming stuff even though I have been working and learning about this for past 4 months and I think I barely scratch the surface known as Excel. And already I am trying to convince myself I need to learn ACCESS for this other project underway.


    Thank you and keep EXCELing,
    David :smile:

    Thanks,
    David
    [size=-2]Bad news. These are the good old days.[/size]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login