| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
clear data and named cell
I know this code below will clear the text in a row.
But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
hi
is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
If you are right and iCurrentMergeStart is the name given to a named range,
then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
hi
you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
Hello,
Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
Remove "all" the named cells on a single worksheet (the one whose row you
cleared) or throughout your entire workbook? Rick "greg" wrote in message ... Hello, Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
just one row.
so Rows(CStr("5:5")).ClearContents "Rick Rothstein (MVP - VB)" wrote in message ... Remove "all" the named cells on a single worksheet (the one whose row you cleared) or throughout your entire workbook? Rick "greg" wrote in message ... Hello, Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
I think I am confused at what you want. Excel has a feature whereby you can
give a range (one or more cells) a Name and then use that name to refer to the range rather than using the range's address. When I just asked you about removing "named cells", you responded with this example... Rows(CStr("5:5")).ClearContents which isn't using a named range at all. That leads me to think you are using the term "named cell" for something other than Excel's named ranges (remember, that range can be a single cell which would make it a named cell). Is that the case? If so, can you explain what you mean when you say "named cell"... exactly what are you referring to when you say that? Rick "greg" wrote in message ... just one row. so Rows(CStr("5:5")).ClearContents "Rick Rothstein (MVP - VB)" wrote in message ... Remove "all" the named cells on a single worksheet (the one whose row you cleared) or throughout your entire workbook? Rick "greg" wrote in message ... Hello, Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
yes, maybe I am being confusing.
I think of a named cell as you can give a cell a specific name. such as C5. and that is MySpecialC5. or something like that. where you can do a Range("MySpecialC5") So I have a row of text and cells that have been "named". the Rows(CStr("5:5")).ClearContents can remove the text in row 5. but the named cells are still there. is there a way to remove both text and the named cells? thanks for all the help "Rick Rothstein (MVP - VB)" wrote in message ... I think I am confused at what you want. Excel has a feature whereby you can give a range (one or more cells) a Name and then use that name to refer to the range rather than using the range's address. When I just asked you about removing "named cells", you responded with this example... which isn't using a named range at all. That leads me to think you are using the term "named cell" for something other than Excel's named ranges (remember, that range can be a single cell which would make it a named cell). Is that the case? If so, can you explain what you mean when you say "named cell"... exactly what are you referring to when you say that? Rick "greg" wrote in message ... just one row. so Rows(CStr("5:5")).ClearContents "Rick Rothstein (MVP - VB)" wrote in message ... Remove "all" the named cells on a single worksheet (the one whose row you cleared) or throughout your entire workbook? Rick "greg" wrote in message ... Hello, Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
Okay, you are using named cells the same way I am, so that is good. Now, to
repeat my question about the named cells... do you want to delete only the named cells on the worksheet that you are on, or do you want to remove every named cell in the workbook (no matter what worksheet they are on)? Rick "greg" wrote in message ... yes, maybe I am being confusing. I think of a named cell as you can give a cell a specific name. such as C5. and that is MySpecialC5. or something like that. where you can do a Range("MySpecialC5") So I have a row of text and cells that have been "named". the Rows(CStr("5:5")).ClearContents can remove the text in row 5. but the named cells are still there. is there a way to remove both text and the named cells? thanks for all the help "Rick Rothstein (MVP - VB)" wrote in message ... I think I am confused at what you want. Excel has a feature whereby you can give a range (one or more cells) a Name and then use that name to refer to the range rather than using the range's address. When I just asked you about removing "named cells", you responded with this example... which isn't using a named range at all. That leads me to think you are using the term "named cell" for something other than Excel's named ranges (remember, that range can be a single cell which would make it a named cell). Is that the case? If so, can you explain what you mean when you say "named cell"... exactly what are you referring to when you say that? Rick "greg" wrote in message ... just one row. so Rows(CStr("5:5")).ClearContents "Rick Rothstein (MVP - VB)" wrote in message ... Remove "all" the named cells on a single worksheet (the one whose row you cleared) or throughout your entire workbook? Rick "greg" wrote in message ... Hello, Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
just in the specified row.
So for instance all the text and named cells in row 5. "Rick Rothstein (MVP - VB)" wrote in message ... Okay, you are using named cells the same way I am, so that is good. Now, to repeat my question about the named cells... do you want to delete only the named cells on the worksheet that you are on, or do you want to remove every named cell in the workbook (no matter what worksheet they are on)? Rick "greg" wrote in message ... yes, maybe I am being confusing. I think of a named cell as you can give a cell a specific name. such as C5. and that is MySpecialC5. or something like that. where you can do a Range("MySpecialC5") So I have a row of text and cells that have been "named". the Rows(CStr("5:5")).ClearContents can remove the text in row 5. but the named cells are still there. is there a way to remove both text and the named cells? thanks for all the help "Rick Rothstein (MVP - VB)" wrote in message ... I think I am confused at what you want. Excel has a feature whereby you can give a range (one or more cells) a Name and then use that name to refer to the range rather than using the range's address. When I just asked you about removing "named cells", you responded with this example... which isn't using a named range at all. That leads me to think you are using the term "named cell" for something other than Excel's named ranges (remember, that range can be a single cell which would make it a named cell). Is that the case? If so, can you explain what you mean when you say "named cell"... exactly what are you referring to when you say that? Rick "greg" wrote in message ... just one row. so Rows(CStr("5:5")).ClearContents "Rick Rothstein (MVP - VB)" wrote in message ... Remove "all" the named cells on a single worksheet (the one whose row you cleared) or throughout your entire workbook? Rick "greg" wrote in message ... Hello, Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
Now to be clear, you want to **delete** the Name from the Names collection
(so that you will no longer see the Name in the Name Box), right? If so, this subroutine can be used to delete all *single* named cells whose Row number is passed in as an argument... Sub RemoveNamedCellsInRow(RowNumber As Long) Dim N As Name For Each N In ActiveWorkbook.Names With N.RefersToRange If .Count = 1 And .Worksheet.Name = ActiveSheet.Name Then If .Row = RowNumber Then N.Delete End If End If End With Next End Sub So, Copy/Paste this subroutine into the code window with the macro you are using to clear the contents of your row of cells. Within that macro, simple call the RemoveNamedCellsInRow passing in the same row number you used in your ClearContents statement. Using the sample line of code you posted in your first message, your code at that location would look like this... Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents RemoveNamedCellsInRow iCurrentMergeStart Rick "greg" wrote in message ... just in the specified row. So for instance all the text and named cells in row 5. "Rick Rothstein (MVP - VB)" wrote in message ... Okay, you are using named cells the same way I am, so that is good. Now, to repeat my question about the named cells... do you want to delete only the named cells on the worksheet that you are on, or do you want to remove every named cell in the workbook (no matter what worksheet they are on)? Rick "greg" wrote in message ... yes, maybe I am being confusing. I think of a named cell as you can give a cell a specific name. such as C5. and that is MySpecialC5. or something like that. where you can do a Range("MySpecialC5") So I have a row of text and cells that have been "named". the Rows(CStr("5:5")).ClearContents can remove the text in row 5. but the named cells are still there. is there a way to remove both text and the named cells? thanks for all the help "Rick Rothstein (MVP - VB)" wrote in message ... I think I am confused at what you want. Excel has a feature whereby you can give a range (one or more cells) a Name and then use that name to refer to the range rather than using the range's address. When I just asked you about removing "named cells", you responded with this example... which isn't using a named range at all. That leads me to think you are using the term "named cell" for something other than Excel's named ranges (remember, that range can be a single cell which would make it a named cell). Is that the case? If so, can you explain what you mean when you say "named cell"... exactly what are you referring to when you say that? Rick "greg" wrote in message ... just one row. so Rows(CStr("5:5")).ClearContents "Rick Rothstein (MVP - VB)" wrote in message ... Remove "all" the named cells on a single worksheet (the one whose row you cleared) or throughout your entire workbook? Rick "greg" wrote in message ... Hello, Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
clear data and named cell
cool thanks.
I will give it a try "Rick Rothstein (MVP - VB)" wrote in message ... Now to be clear, you want to **delete** the Name from the Names collection (so that you will no longer see the Name in the Name Box), right? If so, this subroutine can be used to delete all *single* named cells whose Row number is passed in as an argument... Sub RemoveNamedCellsInRow(RowNumber As Long) Dim N As Name For Each N In ActiveWorkbook.Names With N.RefersToRange If .Count = 1 And .Worksheet.Name = ActiveSheet.Name Then If .Row = RowNumber Then N.Delete End If End If End With Next End Sub So, Copy/Paste this subroutine into the code window with the macro you are using to clear the contents of your row of cells. Within that macro, simple call the RemoveNamedCellsInRow passing in the same row number you used in your ClearContents statement. Using the sample line of code you posted in your first message, your code at that location would look like this... Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents RemoveNamedCellsInRow iCurrentMergeStart Rick "greg" wrote in message ... just in the specified row. So for instance all the text and named cells in row 5. "Rick Rothstein (MVP - VB)" wrote in message ... Okay, you are using named cells the same way I am, so that is good. Now, to repeat my question about the named cells... do you want to delete only the named cells on the worksheet that you are on, or do you want to remove every named cell in the workbook (no matter what worksheet they are on)? Rick "greg" wrote in message ... yes, maybe I am being confusing. I think of a named cell as you can give a cell a specific name. such as C5. and that is MySpecialC5. or something like that. where you can do a Range("MySpecialC5") So I have a row of text and cells that have been "named". the Rows(CStr("5:5")).ClearContents can remove the text in row 5. but the named cells are still there. is there a way to remove both text and the named cells? thanks for all the help "Rick Rothstein (MVP - VB)" wrote in message ... I think I am confused at what you want. Excel has a feature whereby you can give a range (one or more cells) a Name and then use that name to refer to the range rather than using the range's address. When I just asked you about removing "named cells", you responded with this example... which isn't using a named range at all. That leads me to think you are using the term "named cell" for something other than Excel's named ranges (remember, that range can be a single cell which would make it a named cell). Is that the case? If so, can you explain what you mean when you say "named cell"... exactly what are you referring to when you say that? Rick "greg" wrote in message ... just one row. so Rows(CStr("5:5")).ClearContents "Rick Rothstein (MVP - VB)" wrote in message ... Remove "all" the named cells on a single worksheet (the one whose row you cleared) or throughout your entire workbook? Rick "greg" wrote in message ... Hello, Thanks for the help. Yes, clear the data and named cell. I really don't want to delete the row. But clear the data. and also remove all the named cells. "FSt1" wrote in message ... hi you may be right. but from his title "clear data AND Named cell", i assumed that was what it wanted. he is already clearing the data. and i did ask. is that the name? question not clearly stated Regards FSt1 "Rick Rothstein (MVP - VB)" wrote: If you are right and iCurrentMergeStart is the name given to a named range, then I would think (based on how the OP phrased his message) that he would be looking for this... Range("iCurrentMergeStart").ClearContents or, if iCurrentMergeStart is a variable containing the name, then this... Range(iCurrentMergeStart).ClearContents rather than a method to physically delete the name from the Names collection. But then again, you might be right as the question was not very clearly stated to me. Rick "FSt1" wrote in message ... hi is iCurrentMergeStart the name? if so then.... ActiveWorkbook.Names("iCurrentMergeStart").Delete regards FSt1 "greg" wrote: I know this code below will clear the text in a row. But it will not clear out the named cells. Is there a way to clear both? Rows(CStr(iCurrentMergeStart & ":" & iCurrentMergeStart)).ClearContents |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transfer a name from one cell to another but leave clear if clear? | Excel Worksheet Functions | |||
| Clear cell data using Combo Box | Excel Programming | |||
| Clear Named Ranges | Excel Programming | |||
| VBA Macro to Clear Named Cell Contents | Excel Programming | |||
Linear Mode
