Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had previously been filtered AGAINST? | Excel Discussion (Misc queries) | |||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row | Excel Worksheet Functions | |||
find values in multiple cells and paste row values | Excel Discussion (Misc queries) | |||
There must be a way!!!--set cells' values equal to calculated values in another range | Excel Programming | |||
average values in non-contiguous cells, ignoring 0 values | Excel Worksheet Functions |