1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Copy data without the Blanks

Discussion in 'Business Applications' started by Aspire2excelance, Apr 15, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Aspire2excelance

    Aspire2excelance Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    7
    Hi All, a Newbie here, can anyone please help me. I have uploaded a file named “removing blanks and recording data.xls. on which I am trying to list the ID’s of Clients and how much they owe. My problem is that I wish from Cell F14 of the Income Sheet to list, without any blanks, any amounts found in column M of the Payments Sheet and then in Cell E14 of the Income Sheet to record the associated data found in Column E of the Payments Sheet. I’ve checked my formulas textbooks and googled but can’t get close to a solution. Any ideas would be appreciated.
     

    Attached Files:

  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    You can easily do this with a macro. Is that an option for you?

    Rollin
     
  3. Aspire2excelance

    Aspire2excelance Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    7
    Thanks for replying so promptly, yes a macro would be an option
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Does the data need to appear in any particular order once transfered to the income sheet? (highest to lowest)

    Rollin
     
  5. Aspire2excelance

    Aspire2excelance Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    7
    Preferably in the same order of appearance as in the Payments sheet, thanks Rollin
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try this macro

    Code:
    Sub CopyData()
    
    Sheets("Income").Rows("14:" & Sheets("Income").Rows.Count).Delete
    
    For Each vCell In Sheets("Payments").Range("M3:M" & Cells(Rows.Count, "M").End(xlUp).Row)
    
    If vCell <> ""  Then
    
    If Trim(vCell) <> "" Or IsEmpty(vCell) Then
    
    Sheets("Income").Range("F14").Value = vCell
    Sheets("Income").Range("E14").Value = Sheets("Payments").Range("E" & vCell.Row).Value
    
    Else
    
    Sheets("Income").Range("F" & Cells(Rows.Count, "F").End(xlUp).Row + 1).Value = vCell
    Sheets("Income").Range("E" & Cells(Rows.Count, "F").End(xlUp).Row).Value = Sheets("Payments").Range("E" & vCell.Row).Value
    
    End If
    
    End If
    
    Next vCell
    
    End Sub
    Rollin
     
  7. Aspire2excelance

    Aspire2excelance Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    7
    Thanks Rollin,
    I've pasted it into a new module but when activated it produces on the Income sheet the 1st line of data only (Column E and M of Row 3)
     
  8. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    For some reason the compiler is having a hard time determining the last row of data used in the workbook. I've tried at least 4 different ways of returning the last row programatically but none are working. In the meantime you can just hard code the last row to evaluate and you should be ok. Just change the FOR LOOP so that the last row of data to evaluate is hard coded as I've shown below. I'll keep researching why the last row can't be determined programatically.

    For Each vCell In Sheets("Payments").Range("M3:M70")
     
  9. Aspire2excelance

    Aspire2excelance Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    7
    I've input M70 and now when I run the macro I get Client13_Jan and 15 in columns E and F on row 14 of the Icome page and row 15 shows just Ltr1_Jan on column E.
     
  10. Aspire2excelance

    Aspire2excelance Thread Starter

    Joined:
    Apr 15, 2010
    Messages:
    7
    Rollin it would be great if I could hard code the last row of data because I actually have 12 Income sheets, one for each month. Thanks again for your help
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/917063

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice