+ Reply to Thread
Results 1 to 5 of 5

If data in any cell of the row, copy to the sheet2

  1. #1
    Registered User
    Join Date
    09-01-2011
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    11

    If data in any cell of the row, copy to the sheet2

    Hi,

    I have a table ranging from A1:AF3500 where there are data scattered around in different cells, and many of the rows are completely empty. What I want to do is to delete all these rows, or copy all the rows with data to a new sheet (think the last one is the easiest?).

    The tricky thing is that I can't look at just one column for data, since the data is all over the range of each row. So i need to search every row to see if there are any data in any of the cells, if yes, then copy to the other sheet.

    Been trying macro but I don't have much skills in programming.

    Is there anyone that can help me?

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: If data in any cell of the row, copy to the sheet2

    This will delete all the blank rows:

    Please Login or Register  to view this content.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    09-01-2011
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    11

    Smile Re: If data in any cell of the row, copy to the sheet2

    Thank you very much!

    Follow up question.
    If I have data looking like this:

    ______A_______B________C
    1 _System A
    2 __________Equip. 1
    3 ____________________Item 1
    4 ____________________Item 2
    5 __________Equip. 2
    6 ____________________Item 1
    7_System B

    etc.

    How can I get "System A" to fill out all the rows in "Column A" (with "System A") down to "System B" (Then System B to System C etc.). And again how to fill "SubEquip." down to next type "SubEquip." etc.
    Thank you!
    Last edited by Stiansen; 09-01-2011 at 08:50 AM.

  4. #4
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: If data in any cell of the row, copy to the sheet2

    Can you load a sample workbook showing the layout of the data as it is and the desired result?

    Dom

  5. #5
    Registered User
    Join Date
    09-01-2011
    Location
    Norway
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: If data in any cell of the row, copy to the sheet2

    I looked at the workbook again and the your delete code worked perfectly, but the data is scattered all over from A:AF and there is no logic to it so I will instead contact my supplier and tell them to work it out!

    But thanks alot for the help, and I'll prolly be back soon with more questions!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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