Find rows containing data and copy to next blank row

[Woody75]

Hi all,

Does anyone have a macro that will do the following??

In table 1 you will see that not all rows contain data (this is because i have removed it previously with a macro) the second table shows what i want the macro to do but without deleting rows:

	A	Hiddenrow with formula	B	C	D	E	Hiddenrow with formula	F
1	Order No	Hidden	Name	Address 1	Address 2	Post Code	Hidden	Dispatched
2
3	12345		Smith	10 The Place	LONDON	SW1 1SW		No
4
5	98765		Jones	9 The Place	Mancs	M15 6TH		No
6
7	45678		Brown	Blah blah	Blah	Blah		No

<tbody>
</tbody>

	A	Hiddenrow with formula	B	C	D	E	Hiddenrow with formula	F
1	Order No	Hidden	Name	Address 1	Address 2	Post Code	Hidden	Dispatched
2	12345		Smith	10 The Place	LONDON	SW1 1SW		No
3	98765		Jones	9 The Place	Mancs	M15 6TH		No
4	45678		Brown	Blah blah	Blah	Blah		No
5
6

<tbody>
</tbody>

So basically i would like a macro to search for the first none blank value in column A and copy the row to the first available blank row , it also needs to blank out that row once done. i.e row 3 would be moved up to row 2, 5 up to 3, 7 up to 4 etc etc.

The hidden rows contain formulas which cannot be deleted or my ws goes Pete Tongue.

Many Thanks

Woody

 

[FormR]

The hidden rows contain formulas which cannot be deleted or my ws goes Pete Tongue.

Can post those formulas? Someone may be able to help make them more robust to row deletions.

 

[Woody75]

Can post those formulas? Someone may be able to help make them more robust to row deletions.

I just used the tables above as examples, the actual formulas as per my ws are as follows:

Cell E3:E3002 =IF(D3="","",MONTH(D3)) - calculates month from date in D10, which is then compared to the month of =Today() - this is used to sort data being shown in a sep ws.

Cells M3:M3002 =IF(F3="","",IF(L3>0,"YES","NO")) - basic If cmd that tells other ws whether that line of info has been dealt with. This links into the same 'other' ws mentioned above to filter results.

Cheers

 

[FormR]

These don't look vulnerable to row deletions - maybe the formula in the "other" worksheet are causing the problem?

 

[Woody75]

Agreed, but I would like the number of rows to remain constant, the only thing I need the macro to do is find the first Non-Blank Value of column A (Top to Bottom) and paste the row info from column A?:K? in the first blank row. It would then need to loop through both actions until completed.

 

[FormR]

Is there a column that you can sort on to move the blank rows to the bottom?

 

[Woody75]

Is there a column that you can sort on to move the blank rows to the bottom?

Hmmm, hadn't thought of that... the columns do indeed have an autofilter, but the first column (A) is numbered 1 to 3000, and i'd like to keep it that way.

whats the best way to apply an autofilter to say Range B2:G3002 but only filter by column F to get the blanks at the bottom?