copy and paste special and remove blank lines. 3

[thepunisher]

hi,

I have the following....

17513

296734978

3986568

9328059

etc...

I want to copy and paste this into a spreadsheet, but i want the blank lines removed so it looks like this...

17513
296734978
3986568
9328059

etc..

Does anyone know how to do this?

The 'Remove blanks' option in paste special doesnt seem to work!

thanks,

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[Loomah]

Hi
To remove the blanks before copying you could use the following

If your data is only in one column (eg all in col A) then use this method

Sub zero()
Dim lRow As Long
lRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

Range("A1:A" & lRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete Shift:=xlUp

End Sub

If you have data in many columns and only want to remove the rows that are entirely blank then you could use this method

Sub zero2()
Dim lRow As Long
Dim lCnt As Long

lRow = Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For lCnt = lRow To 1 Step -1
    If CountA(Rows(lCnt)) = 0 Then
        Rows(lCnt).EntireRow.Delete
    End If
Next

End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[thepunisher]

The data is in column A and is permanent.It will not change.

When i run method 1 it simply deletes everything in the row.

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[thepunisher]

I would prefer if i didnt have to do this using code

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[Loomah]

Without code one labourious way is

1) Select all the data required
2) Go DATA>FILTER>AUTO FILTER
3) Select "Non Blanks"
4) Go EDIT>GO TO>Click "Special">Select "Visible cells only">OK
or use ALT+;
5) Copy
6) Select destination & paste

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[thepunisher]

When i try that it goes like this....

17513
296734978
3986568
9328059

which is great.

but the row numbers on the left are like this
1 | 17513
3 | 296734978
5 | 3986568
7 | 9328059

I want the rows to go ....

1 | 17513
2 | 296734978
3 | 3986568
4 | 9328059
etc...

How is this done?

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[GlennUK]

Hi there "thepunisher",

if you complete Loomahs directions, then after pasting you should have rows the way you want. It sounds like you're complaining after just doing the Filtering.

Cheers, Glenn.

 

[thepunisher]

nope im doing it as i was told.

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[GlennUK]

Please check Loomah's directions again, carefully, as you cannot have pasted into a new worksheet and have the rows as you have described. When pasting into a new worksheet the rows will be numbered 1, 2, 3, 4, and so on.

Glenn.

 

[dhulbert]

If you just use Data Sort then you can sort out the blanks before or after you copy and paste.

DH.

 

[thepunisher]

ok when i copy a message states that Excel cannot create or use the data range because it is too complex.

Is this why it isnt working?

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[thepunisher]

The problem is the same as i posted earlier.

I cant use data sort as i need the values in the order they are in.

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[GlennUK]

How many rows in total are there? How many of the rows are blanks?

Glenn.

 

[thepunisher]

There is about 35000 rows, every second one is blank

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[GlennUK]

In that case copy about 5000 ( i.e. 10000 rows filtered ) at a time.

Glenn.

 

[thepunisher]

Will do thanks for your patience

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[thepunisher]

works like a charm.

Chief Dan George-Get ready little lady, hell is coming to breakfast

 

[GlennUK]

Hooray

Will treat myself to a drink tonight.

Cheers, Glenn.

 

[hobman]

thepunisher,

I know you already solved your problem but this also works and might be easier.

Sub DeletingBlanks()
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Just record this macro and program it so when you click a button it gets executed.

Cheers!!!