ignore formulas

tony.reynolds

Board Regular
Joined
Jul 8, 2010
Messages
97
I have a macro that selects all cells that have data in a column. the only problem is my cells have a formula and it selects these as well even when the formular results in ""

formular in the cell is =IF(B13="","",C13&","&B13)

Macro is

Range(ActiveCell, (Cells(65536, ActiveCell.Column).End(xlUp))).Copy
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
where is it going to be copied. suppose it is to be pasted to A1 in sheet 2 the code statement will be like this (modify to suit you)-remember which cell you select becomes active cell.

Code:
Sub test()
Worksheets("sheet1").Activate
Range(ActiveCell, (Cells(65536, ActiveCell.Column).End(xlUp))).Copy
Worksheets("sheet2").Range("A1").PasteSpecial , Paste:=xlPasteValues

End Sub

only values not formulas will be pasted in sheet 2.
 

Forum statistics

Threads
1,143,654
Messages
5,720,109
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top