+ Reply to Thread
Results 1 to 33 of 33

Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed Col

  1. #1
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Post Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed Col

    Hi,

    Some one Please help me to create VBA for the below.

    I have large data in excel. I have to filter non blank cells of particulars column and need to paste in next work sheet.
    INPUT DATA
    Sample Image.JPG

    In Below Example Column C to F is variable data.
    Column A & B is fixed Data
    I need to filter C coulmn with non blank cell, then i need to paste this in new worksheet along with fixed column of A&B. Like this need to do for remaining columns of D, E and F.

    OUTPUT DATA
    Sample Image Output.JPG

    Some one please help. currently i am doing this manually taking more time
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by ayyappan80; 04-27-2020 at 05:03 AM.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    One way...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Thank you so much for your help. I am getting error on the below row
    .AutoFilter i, "<>0", , "<>"

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Works for me in your upload...Here is another option...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Yes perfectly working as expected in sample file
    But in my file its not working both Codes i am now getting error of "Variable not found" in below row
    "With Sheet2"

    and second code
    sheet2.Range

  6. #6
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Another option

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Yes perfectly working as expected in sample file
    But in my file its not working
    So actual file is not as per sample...in sample file Output tab is sheet2...Check what the actual sheet numbers are and amend...or
    Check if change Sheet2 change to Sheets("Output") works

  8. #8
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Thank you so much. The Last Code is working perfectly. Thanks for your help

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    So what was the actual sheet number of output sheet?

  10. #10
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Before Running the Macro. First i am creating "worksheet in the name of "Output" then only its working.
    and is it possible to modify the code by selecting the data and run? instead of permanently fixing the cell reference of Cell A3 to F3. if possible please help on this. existing output enclosed Attachment 674830

  11. #11
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Hi This code is working fine and helped. But data in A2 is for your reference purpose i added. its not there in actual. if remove row 2 then its skipping to next row for copy. Can you please change the code to start with A3. and is it possible to modify the code by selecting the data and run? instead of permanently fixing the cell reference of Cell A3 to F3. if possible please help on this

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Attachment not working and not sure which or who's code you are referring...

  13. #13
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Mr.nankw83 code is working.

    for your code though i created Sheet2 and Output sheet but its not working.its showing error of variable not defined.Attachment 674865
    Attached Images Attached Images

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    i created Sheet2 and Output sheet
    Output sheet is not sheet2
    Means that Sheet2 does not exist...hence Post 7

    Please Login or Register  to view this content.
    Attachment still not working...See Top Yellow Banner...

    Anyway nankw83 code working so I'll allow him to carry on with thread...
    Last edited by sintek; 04-28-2020 at 04:20 AM.

  15. #15
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    I have adjusted the code to ignore the first row which I think you kept only for descripting your issue. Also, the code is dynamic now where you can place the data in any cell & select at least one cell of the data then run the code of course results will be in sheet Output I've attached a sample file for your reference. Let me know how it goes

    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    @ nankw83
    code is dynamic now
    What if selection is more than 4 columns?

  17. #17
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    @sintek

    It doesn't matter as I have used the following line in my loop for number of columns
    Please Login or Register  to view this content.
    Output sheet is fixed at 4 columns irrespective of number of columns in original data set

  18. #18
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Hi nankw83

    Perfectly working as expected. now its working any sheet and any rows. Thank you so much for your help

  19. #19
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Glad to help & you are welcome

  20. #20
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,160

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    @ nankw83...You are correct...my bad...missed that line...

  21. #21
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    can i have one more help please. on the out put column D, can you please separate negative and positive number separately. Means Column D for Possitive numbers and Column E for Negative numbers.

  22. #22
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    @sintek

    @ayyappan80

    Try below code

    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Hi, nankw83

    Thanks its working fine. i am getting positive and negative separately. once again thanks for your help.

  24. #24
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Hi, nankw83
    On the Column E where we updated negative amount. I want to shown as positive numbers. (After updating the number (-) to be removed)

    the below changes will not work for that?

    b(i, 5) = IIf(a(x, y) < 0, a(x, y), "").SpecialCells(xlCellTypeConstants, 1)

  25. #25
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Try like this

    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    thanks nankw83. Its worked.

  27. #27
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Hi Nankw83 I want to little modify the code. can you please help

    on the below line of Code need to make one change

    b(i, 1) = a(1, Y)
    b(i, 2) = "'" & "000"

    if i,1 ="101" then i,2 to be updated as "002",
    if i,1 ="102" then i,2 to be updated as "003"

    else it to be updated by default "000"

    Currently the above line of code is updating by default "000"

  28. #28
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    The code is taking the value of b(i,1) from a(1,y) which is the location/header name based on your sample & b(i,2) is taking the account number. Does the "000" comes from the header ? Also, does it have to be only those 3 values "000", "002", "003" or the keeps adding up ? It is not clear to me

    Can you upload a sample file along with the expected results
    If I was able to help, you can thank me by clicking the * Add Reputation under my user name

  29. #29
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Hi You are correct. Its Location header only.
    My Actual template having location code instead name .Location will be like this 101 103 100 102

    b(i, 2) = i am expecting as below

    if location code is "101" then "002",
    if its "103" then "000",
    if its 100 then"001"
    if its 102 then"003"
    if location code other than 101 103 100 or 102 then default '000'
    Last edited by ayyappan80; 05-18-2020 at 08:07 AM.

  30. #30
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Ok how about this ?

    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Hi Nankw83 could not get the result. attached sample file for your reference
    Attached Files Attached Files

  32. #32
    Valued Forum Contributor
    Join Date
    08-31-2015
    Location
    Kuwait
    MS-Off Ver
    365
    Posts
    761

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Ok, looks like I have mixed up between first & 2nd column. Check the below code

    Please Login or Register  to view this content.

  33. #33
    Forum Contributor
    Join Date
    12-18-2019
    Location
    chennai
    MS-Off Ver
    excel 2010
    Posts
    107

    Re: Filter Non Blank Cells for Specific Column and paste in new worksheet along with Fixed

    Hi Nankw83

    Perfectly working thank you so much for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] code find last cell in a specific column in specific worksheet and paste value
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2017, 10:38 AM
  2. vba code to filter blank cells in a column only if the column has blanks cells
    By meus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-28-2016, 07:05 AM
  3. [SOLVED] VBA to Filter out Blank Cells, copy /paste into another worksheet
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2014, 03:36 AM
  4. [SOLVED] vba to copy non blank cells and paste to other worksheet
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2013, 06:22 PM
  5. Copy valule from one column and then paste only blank cell to other worksheet
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-16-2013, 12:42 PM
  6. VBA code need for advance filter non blank cells, paste data to another sheet
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2012, 12:15 PM
  7. Macro-Copy&Paste Fixed Data Ranges Into Last Empty Cell of Specific Column
    By hailnorm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-26-2009, 10:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1