Only cells with values

  • Thread starter Thread starter bones288
  • Start date Start date
B

bones288

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.
 
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.
Click to expand...

Have a look in the vba help for specialcells
 
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" <[email protected]>
wrote in message
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.
Click to expand...

Have a look in the vba help for specialcells
 
Don,
I've found that SpecialCells inXL2010 can hang sometimes for minutes whentrying 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" <[email protected]>
wrote in message





Have a look in the vba help for specialcells
Click to expand...

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