How to select cells with values only (not empty ones)?
 

My problem is that I am using a macro to copy data from one workbook and to
paste into the next, however along with the data I need come about 30,000
blank rows, making the file large.

I could use Go Toblanks and Delete the 30,000 once pasted but that takes
alot of time to sit through.

Idealy I could use Go To(Insert something that selects only non-empty cells)

And then copy/paste that into the new workbook.

How to select cells with values only (not empty ones)?
 

Are the rows really blank ? and are they between the data or below the data

You can use AutoFilter in the code and filter for non blanks and copy to the other sheet

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nick" wrote in message ...
My problem is that I am using a macro to copy data from one workbook and to
paste into the next, however along with the data I need come about 30,000
blank rows, making the file large.

I could use Go Toblanks and Delete the 30,000 once pasted but that takes
alot of time to sit through.

Idealy I could use Go To(Insert something that selects only non-empty cells)

And then copy/paste that into the new workbook.

How to select cells with values only (not empty ones)?
 

They are blank and they are below all of the data, I would need an example
autofilter line, because I am having trouble executing it manually and do not
physically write the macro but act it out and make small edits if I have to.

Thanks

"Ron de Bruin" wrote:

Are the rows really blank ? and are they between the data or below the data

You can use AutoFilter in the code and filter for non blanks and copy to the other sheet

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nick" wrote in message ...
My problem is that I am using a macro to copy data from one workbook and to
paste into the next, however along with the data I need come about 30,000
blank rows, making the file large.

I could use Go Toblanks and Delete the 30,000 once pasted but that takes
alot of time to sit through.

Idealy I could use Go To(Insert something that selects only non-empty cells)

And then copy/paste that into the new workbook.

How to select cells with values only (not empty ones)?
 

The blank fields are blank and below all the of the data I need (not mixed in
between)

I would need an autofilter example, because I can't actually execute it,
unless I am missing something and do not write the macros myself, just make
them and do small edits.

"Ron de Bruin" wrote:

Are the rows really blank ? and are they between the data or below the data

You can use AutoFilter in the code and filter for non blanks and copy to the other sheet

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nick" wrote in message ...
My problem is that I am using a macro to copy data from one workbook and to
paste into the next, however along with the data I need come about 30,000
blank rows, making the file large.

I could use Go Toblanks and Delete the 30,000 once pasted but that takes
alot of time to sit through.

Idealy I could use Go To(Insert something that selects only non-empty cells)

And then copy/paste that into the new workbook.

How to select cells with values only (not empty ones)?
 

OK, try this for column A on "Sheet1"
(A1 is the header cell of the column)


Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range

Set WS = Sheets("sheet1") '<<< Change
'A1 is the top left cell of your filter range and the header of the first column
Set rng = WS.Range("A:A")

'Close AutoFilter first
WS.AutoFilterMode = False

'This example filter on the first column in the range (change the field if needed)
rng.AutoFilter Field:=1, Criteria1:="<" & ""

Set WSNew = Worksheets.Add

WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

WS.AutoFilterMode = False

End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nick" wrote in message ...
They are blank and they are below all of the data, I would need an example
autofilter line, because I am having trouble executing it manually and do not
physically write the macro but act it out and make small edits if I have to.

Thanks

"Ron de Bruin" wrote:

Are the rows really blank ? and are they between the data or below the data

You can use AutoFilter in the code and filter for non blanks and copy to the other sheet

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Nick" wrote in message ...
My problem is that I am using a macro to copy data from one workbook and to
paste into the next, however along with the data I need come about 30,000
blank rows, making the file large.

I could use Go Toblanks and Delete the 30,000 once pasted but that takes
alot of time to sit through.

Idealy I could use Go To(Insert something that selects only non-empty cells)

And then copy/paste that into the new workbook.