Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying values skipping blanks
I have a macro that is supposed to create an order form based on our current
inventory. I use a script that I got help on from this forum. When I run this macro, sometimes it works, (It is supposed to copy a cell value created from a formula and place it into another column skipping all blank cells) Sometimes it copies the formula in the cell instead of the value. What would I need to change to get it to copy only the VALUE to the new cell? Macro example below: Sub OfficeDepotDB() Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim StartRow As Long Dim EndRow As Long Dim rng As Range With Application CalcMode = .Calculation ..Calculation = xlCalculationManual ..ScreenUpdating = False End With ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView With ActiveSheet ..DisplayPageBreaks = False StartRow = 1 EndRow = 50 For Lrow = StartRow To EndRow Step 1 If IsError(.Cells(Lrow, "K").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "K").Value < "" Then If rng Is Nothing Then Set rng = .Cells(Lrow, "K") Else Set rng = Application.Union(rng, .Cells(Lrow, "K")) End If End If Next End With If Not rng Is Nothing Then rng.Copy Range("L3") ActiveWindow.View = ViewMode With Application ..ScreenUpdating = True ..Calculation = CalcMode End With End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skipping Blanks (Again) | Excel Discussion (Misc queries) | |||
Skipping Blanks | Excel Discussion (Misc queries) | |||
Copying values but skipping empty cells | Excel Worksheet Functions | |||
Skipping blanks | Excel Discussion (Misc queries) | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |