copy/paste column remove formulas
 

I need to copy a column and paste it special to remove formulas. The
columns position is variable.

here's what i've tried so far

*--remove formula for totals
IF lnTotalColumn 1
loXLSheet.Columns(lnTotalColumn).Copy
*!* loXLSheet.Range(Get_Col(lnTotalColumn)+
"1").PasteSpecial(xlPasteValues)
*!* loXLSheet.Columns(lnTotalColumn).PasteSpecial(xlPa steValues)
loXLSheet.Range(Get_Col(lnTotalColumn)+ "1").Select
loXLSheet.Application.Selection.PasteSpecial(xlPas teValues)
ENDIF


Most threw error:
OLE IDispatch exception code 0 from Microsoft Office Excel: This
operation requires the merged cells to be identically sized... (1429)

I'd rather not use selection.

copy/paste column remove formulas
 

Sub columntovalues()
Columns("m").Value = Columns("m").Value
End Sub
or
Columns(activecell.column).Value = Columns(activecell.column).Value

--
Don Guillett
SalesAid Software

"sugargenius" wrote in message
oups.com...
I need to copy a column and paste it special to remove formulas. The
columns position is variable.

here's what i've tried so far

*--remove formula for totals
IF lnTotalColumn 1
loXLSheet.Columns(lnTotalColumn).Copy
*!* loXLSheet.Range(Get_Col(lnTotalColumn)+
"1").PasteSpecial(xlPasteValues)
*!* loXLSheet.Columns(lnTotalColumn).PasteSpecial(xlPa steValues)
loXLSheet.Range(Get_Col(lnTotalColumn)+ "1").Select
loXLSheet.Application.Selection.PasteSpecial(xlPas teValues)
ENDIF


Most threw error:
OLE IDispatch exception code 0 from Microsoft Office Excel: This
operation requires the merged cells to be identically sized... (1429)

I'd rather not use selection.

copy/paste column remove formulas
 

Don Guillett wrote:
Sub columntovalues()
Columns("m").Value = Columns("m").Value
End Sub
or
Columns(activecell.column).Value = Columns(activecell.column).Value

--
Don Guillett
SalesAid Software

"sugargenius" wrote in message
oups.com...
I need to copy a column and paste it special to remove formulas. The
columns position is variable.

here's what i've tried so far

*--remove formula for totals
IF lnTotalColumn 1
loXLSheet.Columns(lnTotalColumn).Copy
*!* loXLSheet.Range(Get_Col(lnTotalColumn)+
"1").PasteSpecial(xlPasteValues)
*!* loXLSheet.Columns(lnTotalColumn).PasteSpecial(xlPa steValues)
loXLSheet.Range(Get_Col(lnTotalColumn)+ "1").Select
loXLSheet.Application.Selection.PasteSpecial(xlPas teValues)
ENDIF


Most threw error:
OLE IDispatch exception code 0 from Microsoft Office Excel: This
operation requires the merged cells to be identically sized... (1429)

I'd rather not use selection.


Thanks for the suggestion Don. It doesn't work via OLE for some
reason. I get error

Array dimensions are invalid. (230)

If I loop through each cell, it works:

FOR x=4 TO loXLSheet.Cells(65536, lnTotalColumn).End(xlUp).Row
loXLSheet.Cells(x,lnTotalColumn).Value =
loXLSheet.Cells(x,lnTotalColumn).Value
ENDFOR