Click here for important information about Computing.net.

I want to copy text/value and skip blanks between worksheets

Tags:
worksheet
macros
October 23, 2012 at 01:17:41
Specs: Windows 7
How do I create macro to copy only text/value and not blanks from worksheet1 to worksheet2? Hope my explanation below is clear. Thanks.

Example:
Worksheet 1:
A1 = text/value1
A2=text/value2
A3=blank cell
A4=blank cell
A5=blank cell
A6=text value3
A7=blank cell
A8=text value4
A9=blank cell
A10=text value5
A11=blank cell
A12=text value6

Worksheet 2:
A1 = text/value1
A2=text/value2
A3=text value3
A4=text value4
A5=text value5
A6=text value6


See More: I want to copy text/value and skip blanks between worksheets

Report •

#1
October 23, 2012 at 06:03:08
Try this:

1 - Copy Column A from Sheet 1 to Sheet 2.
2 - In Sheet2!B1 Enter this formula and drag it down. You'll get Row numbers next to the non-empty cells.

=IF(A1<>"",ROW(),"")

3 - Select Columns A & B
4 - Sort on Column B
5 - Delete formulas in Column B

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 1, 2012 at 00:34:50
Not quite the solution I am looking for.

Let me clarify my question again:

I have worksheet 1, column D4-D21, eg;
D4 - Jan purchase 1
D5 - Jan purchase 2
D6 - Jan purchase 3
D7 - blank
D8 - blank
D9 - blank
D10 - Feb purchase 1
D11 - Feb purcahse 2
D12 - Feb purchase 3
D13 - blank
D14 - blank
D15 - blank
D16 - Mar purchase 1
D17 -blank
D18 - blank
D19 - blank
D20 - blank
D21 - blank

I want to copy to worksheet 2 as follows:-

D4 copy to A4, worksheet 2
D5 copy to A5
D6 copy to A6
skip D7-D9
D10 copy to A7
D11 copy to A8
D12 copy to A9
skip D13-15
process continues

How do I create a macro to copy and paste using loop to skip any blank row and move on to the next row until it finds the next non-blank.

Thanks.



Report •
Start a discussion
Related Solutions
See More ↓


Ask Question