Copy data without the Blanks

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Aspire2excelance

Thread Starter
Joined
Apr 15, 2010
Messages
7
[FONT=&quot]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.[/FONT]
 

Attachments

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

Rollin
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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)
 
Joined
Sep 4, 2003
Messages
4,916
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")
 

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.
 

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top