Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
I need to create a new worksheet in my workbook which is largely a copy of
an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
I may be able to answer my own question: I juts noticed that paste special
has a "skip blanks" box, Imagine I've never realized this in decades of EXCEL! D "Dean" wrote in message ... I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
hey dean - did this work the way you intended?????
thanks susan On Feb 6, 11:50 am, "Dean" wrote: I may be able to answer my own question: I juts noticed that paste special has a "skip blanks" box, Imagine I've never realized this in decades of EXCEL! D "Dean" wrote in message ... I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
Guess you're one step ahead of me! No, it didn't. Also, I saw paste link
which is what I really want but, if you select it, it hushes the skip blanks option, so I still get the zeroes problem. Help, please. Thanks! Dean "Susan" wrote in message oups.com... hey dean - did this work the way you intended????? thanks susan On Feb 6, 11:50 am, "Dean" wrote: I may be able to answer my own question: I juts noticed that paste special has a "skip blanks" box, Imagine I've never realized this in decades of EXCEL! D "Dean" wrote in message ... I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
=IF(ISBLANK(Sheet1!A1),"",Sheet1!A1)
or use this =IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1) then select all cells on the new sheet and do Edit=Goto=Special and select formulas and errors. Then do Edit=Clear Contents That will work if the original sheet didn't have any formulas showing error values. for a macro Make the new sheet the active sheet and run this code. Sub CopyData() Dim rng as Range Dim rng1 as Range Dim cell as Range On Error Resume Next with worksheets("Sheet1") set rng = .cells.Specialcells(xlconstants) set rng1 = .cells.SpecialCells(xlFormulas) End with On Error goto 0 if not rng is nothing then for each cell in rng activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if if not rng1 is nothing then for each cell in rng1 activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if End Sub -- Regards, Tom Ogilvy "Dean" wrote: I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
You can change your formula to:
=if(oldsheet!a1="","",oldsheet!a1) Dean wrote: I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
which if checked disables paste links. If you don't want links, then it is
unclear why copying the sheet didn't do what you wanted. -- Regards, Tom Ogilvy "Dean" wrote: I may be able to answer my own question: I juts noticed that paste special has a "skip blanks" box, Imagine I've never realized this in decades of EXCEL! D "Dean" wrote in message ... I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
I'm amazed that you churn such an answer out for the macro so quickly!
I tried the very clever EXCEL approach and it is really interesting because I've never used this kind of functionality. Still, it leaves me with IF statements in all the non empty cells that I'd rather not have, since the workbook is already a monster. When I copied in the macro approach, the two rows before the end if statement turned red font. Is there some special way to copy macros from posts so that it doesn't stick in extra characters (perhaps when there is a carriage return) or did you perhaps, type in an extra spacebar or something like that? Thanks! Dean "Tom Ogilvy" wrote in message ... =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1) or use this =IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1) then select all cells on the new sheet and do Edit=Goto=Special and select formulas and errors. Then do Edit=Clear Contents That will work if the original sheet didn't have any formulas showing error values. for a macro Make the new sheet the active sheet and run this code. Sub CopyData() Dim rng as Range Dim rng1 as Range Dim cell as Range On Error Resume Next with worksheets("Sheet1") set rng = .cells.Specialcells(xlconstants) set rng1 = .cells.SpecialCells(xlFormulas) End with On Error goto 0 if not rng is nothing then for each cell in rng activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if if not rng1 is nothing then for each cell in rng1 activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if End Sub -- Regards, Tom Ogilvy "Dean" wrote: I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
Actually, I forgot to mention that I did try that. I just want to avoid all
the if statements as the workbook is already huge and complex, although I do still want this page live, i.e., I;m not willing to paste the new results each time something in the workbook changes. Looks like Tom has cooked up a macro based approach which, in this case, will hopefully get around this problem. Normally, I prefer non-macro approaches, since I';m not good at them, but this is an exception. Thanks Dave, Dean "Dave Peterson" wrote in message ... You can change your formula to: =if(oldsheet!a1="","",oldsheet!a1) Dean wrote: I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
Actually, I now realize you have the same statement above, so I should be
able to figure out the problem. Looks like the next statement goes on the next line. Let me try that. "Dean" wrote in message ... I'm amazed that you churn such an answer out for the macro so quickly! I tried the very clever EXCEL approach and it is really interesting because I've never used this kind of functionality. Still, it leaves me with IF statements in all the non empty cells that I'd rather not have, since the workbook is already a monster. When I copied in the macro approach, the two rows before the end if statement turned red font. Is there some special way to copy macros from posts so that it doesn't stick in extra characters (perhaps when there is a carriage return) or did you perhaps, type in an extra spacebar or something like that? Thanks! Dean "Tom Ogilvy" wrote in message ... =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1) or use this =IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1) then select all cells on the new sheet and do Edit=Goto=Special and select formulas and errors. Then do Edit=Clear Contents That will work if the original sheet didn't have any formulas showing error values. for a macro Make the new sheet the active sheet and run this code. Sub CopyData() Dim rng as Range Dim rng1 as Range Dim cell as Range On Error Resume Next with worksheets("Sheet1") set rng = .cells.Specialcells(xlconstants) set rng1 = .cells.SpecialCells(xlFormulas) End with On Error goto 0 if not rng is nothing then for each cell in rng activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if if not rng1 is nothing then for each cell in rng1 activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if End Sub -- Regards, Tom Ogilvy "Dean" wrote: I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
Yup, that did it. Worked like a charm.
Thanks to everyone who helped! Dean "Dean" wrote in message ... Actually, I now realize you have the same statement above, so I should be able to figure out the problem. Looks like the next statement goes on the next line. Let me try that. "Dean" wrote in message ... I'm amazed that you churn such an answer out for the macro so quickly! I tried the very clever EXCEL approach and it is really interesting because I've never used this kind of functionality. Still, it leaves me with IF statements in all the non empty cells that I'd rather not have, since the workbook is already a monster. When I copied in the macro approach, the two rows before the end if statement turned red font. Is there some special way to copy macros from posts so that it doesn't stick in extra characters (perhaps when there is a carriage return) or did you perhaps, type in an extra spacebar or something like that? Thanks! Dean "Tom Ogilvy" wrote in message ... =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1) or use this =IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1) then select all cells on the new sheet and do Edit=Goto=Special and select formulas and errors. Then do Edit=Clear Contents That will work if the original sheet didn't have any formulas showing error values. for a macro Make the new sheet the active sheet and run this code. Sub CopyData() Dim rng as Range Dim rng1 as Range Dim cell as Range On Error Resume Next with worksheets("Sheet1") set rng = .cells.Specialcells(xlconstants) set rng1 = .cells.SpecialCells(xlFormulas) End with On Error goto 0 if not rng is nothing then for each cell in rng activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if if not rng1 is nothing then for each cell in rng1 activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if End Sub -- Regards, Tom Ogilvy "Dean" wrote: I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
In the second loop, the "next" got wrapped to the previous line
if not rng1 is nothing then for each cell in rng1 activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if should be if not rng1 is nothing then for each cell in rng1 activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if Just for completeness, I believe you figured it out. -- regards, Tom Ogilvy "Dean" wrote in message ... Yup, that did it. Worked like a charm. Thanks to everyone who helped! Dean "Dean" wrote in message ... Actually, I now realize you have the same statement above, so I should be able to figure out the problem. Looks like the next statement goes on the next line. Let me try that. "Dean" wrote in message ... I'm amazed that you churn such an answer out for the macro so quickly! I tried the very clever EXCEL approach and it is really interesting because I've never used this kind of functionality. Still, it leaves me with IF statements in all the non empty cells that I'd rather not have, since the workbook is already a monster. When I copied in the macro approach, the two rows before the end if statement turned red font. Is there some special way to copy macros from posts so that it doesn't stick in extra characters (perhaps when there is a carriage return) or did you perhaps, type in an extra spacebar or something like that? Thanks! Dean "Tom Ogilvy" wrote in message ... =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1) or use this =IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1) then select all cells on the new sheet and do Edit=Goto=Special and select formulas and errors. Then do Edit=Clear Contents That will work if the original sheet didn't have any formulas showing error values. for a macro Make the new sheet the active sheet and run this code. Sub CopyData() Dim rng as Range Dim rng1 as Range Dim cell as Range On Error Resume Next with worksheets("Sheet1") set rng = .cells.Specialcells(xlconstants) set rng1 = .cells.SpecialCells(xlFormulas) End with On Error goto 0 if not rng is nothing then for each cell in rng activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if if not rng1 is nothing then for each cell in rng1 activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if End Sub -- Regards, Tom Ogilvy "Dean" wrote: I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy only non-empty cells
That is why I suggested this with the extra step to remove those If
statements for blank cells: or use this =IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1) then select all cells on the new sheet and do Edit=Goto=Special and select formulas and errors. Then do Edit=Clear Contents Of course I emphasize selecting all cells (select the gray button in the upper left corner at the intersection of the row and column headers) -- Regards, Tom Ogilvy "Dean" wrote in message ... I'm amazed that you churn such an answer out for the macro so quickly! I tried the very clever EXCEL approach and it is really interesting because I've never used this kind of functionality. Still, it leaves me with IF statements in all the non empty cells that I'd rather not have, since the workbook is already a monster. When I copied in the macro approach, the two rows before the end if statement turned red font. Is there some special way to copy macros from posts so that it doesn't stick in extra characters (perhaps when there is a carriage return) or did you perhaps, type in an extra spacebar or something like that? Thanks! Dean "Tom Ogilvy" wrote in message ... =IF(ISBLANK(Sheet1!A1),"",Sheet1!A1) or use this =IF(ISBLANK(Sheet1!A1),na(),Sheet1!A1) then select all cells on the new sheet and do Edit=Goto=Special and select formulas and errors. Then do Edit=Clear Contents That will work if the original sheet didn't have any formulas showing error values. for a macro Make the new sheet the active sheet and run this code. Sub CopyData() Dim rng as Range Dim rng1 as Range Dim cell as Range On Error Resume Next with worksheets("Sheet1") set rng = .cells.Specialcells(xlconstants) set rng1 = .cells.SpecialCells(xlFormulas) End with On Error goto 0 if not rng is nothing then for each cell in rng activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if if not rng1 is nothing then for each cell in rng1 activesheet.Range(cell.address).formula = "=" & _ cell.Address(0,0,xlA1,True) next end if End Sub -- Regards, Tom Ogilvy "Dean" wrote: I need to create a new worksheet in my workbook which is largely a copy of an existing one. So, I made a copy of the worksheet. Then, in cell A1, I set it equal to the old worksheet's cell A1. That worked fine, but since there are lots of empty spaces in between important rows and columns in the old worksheet, I'd like the new one to have empty spaces there too. When I simply copy the equation =oldsheetA1 from cell A1 of the new sheet, across and down, I get a bunch of zeroes where the old sheet is blank, and I want blanks in the new sheet too. Unfortunately, there are many blanks and they appear almost randomly so it would be very time consuming to simply go to each new empty cell and delete it. Also, sometimes the zeroes are a result of a computation, not merely an indication that the old worksheet's cell was a blank. I was thinking I could use some sort of isempty function but I'm not sure there is such a thing, anyway I'd rather not have RAM wasted on such a thing. I'd be happier to do the copying from cell A1 to the rest of the worksheet with a macro. The macro should simply set newsheet equal to oldsheet whenever old sheet has something in it. If not, it should leave it blank. If it helps, the worksheet has only 250 rows but almost the entire maximum range of columns. Thanks Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Data From Filled to Empty Cells | Excel Discussion (Misc queries) | |||
Copy empty cells from one workbook to another - Code | Excel Programming | |||
Copy empty cells from one workbook to another - Code | Excel Discussion (Misc queries) | |||
Copy empty cells from one worksheet to another | Excel Programming | |||
Macro to copy value in empty cells | Excel Discussion (Misc queries) |