Copying select areas

aaronhowe

New Member
Joined
Oct 16, 2002
Messages
34
I have a macro which copies out text from one sheet and places it into another. I only want the macro to select text when conditions are met (i.e. there is text to copy), but I wasn't able to do that so I just ended up copying an entire area (which is contigious).

Problem is, there are formulas being copied over (an if/isblank formula). Although when I paste into a new sheet I use Paste Special - values only - it carries over what would have been the result of the formula.

To illustrate, if box A1 istext, then box D1 gives me a date and box F1 adds 14 days to that date. If it's blank it shows nothing. When I paste, it carries 01/01/00 and 15/01/00 over respectively. Thus when I sort my sheet I end up with loads of blanks at the top of my list.

How do I program Visual Basic to identify completed cells and copy only those rows to my new sheet...?!

(Thanks for bearing with me!)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
I would approach it a little bit differently. You say that after you paste and sort it, all the blanks rows appear at the top. At that point I would run a macro to delete all those blank lines. Let's say you first line starts on line 1, and the date 01/01/00 appears in column D. Simply put the following loop in the macro:

Range("D1").Select
Do While ActiveCell.Value = 0
Selection.EntireRow.Delete
Loop

This should delete all those blank rows.

Hope this helps.
 

Forum statistics

Threads
1,143,750
Messages
5,720,633
Members
422,294
Latest member
Gemini774

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top