Only cells with values
 

Hello,

I've been experimenting with code:

Sub CopyColumnValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
If sh.UsedRange.Count 1 Then
Last = Lastcol(DestSh)
With sh.Columns("AE:AE")
DestSh.Columns(Last + 1).Resize(, _
.Columns.Count).Value = .Value
End With
End If
End If
Next
Application.ScreenUpdating = True
End Sub

How can I modify this to copy only cells that contain actual values?

Thank you.

Only cells with values
 

On Feb 6, 3:04*pm, bones288 wrote:
Hello,

I've been experimenting with code:

Sub CopyColumnValues()
* * Dim sh As Worksheet
* * Dim DestSh As Worksheet
* * Dim Last As Long
* * If SheetExists("Master") = True Then
* * * * MsgBox "The sheet Master already exist"
* * * * Exit Sub
* * End If
* * Application.ScreenUpdating = False
* * Set DestSh = Worksheets.Add
* * DestSh.Name = "Master"
* * For Each sh In ThisWorkbook.Worksheets
* * * * If sh.Name < DestSh.Name Then
* * * * * * If sh.UsedRange.Count 1 Then
* * * * * * * * Last = Lastcol(DestSh)
* * * * * * * * With sh.Columns("AE:AE")
* * * * * * * * * * DestSh.Columns(Last + 1).Resize(, _
* * * * * * * * * * .Columns.Count).Value = .Value
* * * * * * * * End With
* * * * * * End If
* * * * End If
* * Next
* * Application.ScreenUpdating = True
End Sub

How can I modify this to copy only cells that contain actual values?

Thank you.

Have a look in the vba help for specialcells

Only cells with values
 

Don,
I've found that SpecialCells inXL2010 can hang sometimes for minutes when trying to find
constants/formulas.
Until/unless MS issues a fix I would avoid using SpecialCells in XL2010.
I haven't found any pretty workarounds, only brute force...
reduce the range size if possible, use a variant to hold the range values and loop thru the variant
array.
'---
Jim Cone



"Don Guillett"
wrote in message
...
On Feb 6, 3:04 pm, bones288 wrote:
Hello,

I've been experimenting with code:

Sub CopyColumnValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
If sh.UsedRange.Count 1 Then
Last = Lastcol(DestSh)
With sh.Columns("AE:AE")
DestSh.Columns(Last + 1).Resize(, _
.Columns.Count).Value = .Value
End With
End If
End If
Next
Application.ScreenUpdating = True
End Sub

How can I modify this to copy only cells that contain actual values?

Thank you.

Have a look in the vba help for specialcells

Only cells with values
 

On Feb 7, 12:04*am, "Jim Cone" wrote:
Don,
I've found that SpecialCells inXL2010 can hang sometimes for minutes when trying to find
constants/formulas.
Until/unless MS issues a fix I would avoid using SpecialCells in XL2010.
I haven't found any pretty workarounds, only brute force...
*reduce the range size if possible, use a variant to hold the range values and loop thru the variant
array.
'---
Jim Cone

"Don Guillett"
wrote in ...
On Feb 6, 3:04 pm, bones288 wrote:









Hello,

I've been experimenting with code:

Sub CopyColumnValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name < DestSh.Name Then
If sh.UsedRange.Count 1 Then
Last = Lastcol(DestSh)
With sh.Columns("AE:AE")
DestSh.Columns(Last + 1).Resize(, _
.Columns.Count).Value = .Value
End With
End If
End If
Next
Application.ScreenUpdating = True
End Sub

How can I modify this to copy only cells that contain actual values?

Thank you.

Have a look in the vba help for specialcells

OP didn't mention version but good to know.... Only using 2010 when
client needs it