Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell €“ really need you help
Hey!
Im kind of dummie in VBA. I usually just work with it to create macros. Ive a important challenge now, that is: using an excel sheet with, average, 20000 cells, I need to replace a part of the cell content several times (i.e. if my cell is 01-02-2006, the €œ02€ has to be replaced by €œMA€). Im using the VB6 version and excel 2003. i've tried this code: sub replace() worksheets("book1").select Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ ReplaceFormat:=False end sub() the cells are formatted to date. i've just noticed that the code does work but sometimes, i.e. if i want to replace the content "-02-" to "-MA-", in all my attempts it just worked once. But if i replace "2006" to "-MA-", it work all the time and the replace cells are something like this: "01/02/-MA-", from the previous: "01-02-2006". Even if i replace "0" to MA on "01-02-2006" the result will be: "1/2/2-MA--MA-6" BUt even after all this test and puting the cells on the original values i try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a error message. Can you help me out here?..thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
The "-" parts of your "-02-" may just be due to the date formatting rather
than what is "really" in the cell. When you select such a cell what is in the formula bar? Whatever is up there ("/2/" for me) is what you can find and replace on. -- Jim "Xaraam" wrote in message ... | Hey! | | I'm kind of dummie in VBA. I usually just work with it to create macros. | I've a important challenge now, that is: using an excel sheet with, average, | 20000 cells, I need to replace a part of the cell content several times (i.e. | if my cell is 01-02-2006, the "02" has to be replaced by "MA"). | I'm using the VB6 version and excel 2003. | | i've tried this code: | sub replace() | worksheets("book1").select | Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ | SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ | ReplaceFormat:=False | end sub() | | the cells are formatted to date. | | i've just noticed that the code does work but sometimes, i.e. if i want to | replace the content "-02-" to "-MA-", in all my attempts it just worked once. | But if i replace "2006" to "-MA-", it work all the time and the replace | cells are something like this: "01/02/-MA-", from the previous: "01-02-2006". | Even if i replace "0" to MA on "01-02-2006" the result will be: | "1/2/2-MA--MA-6" | | BUt even after all this test and puting the cells on the original values i | try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a | error | message. | | Can you help me out here?..thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
In the formula bar is really "01-02-2006" exactly the same thing that in the
cell. but also my intention is to substitute "-02-" including the "-" coz i have other row that can have between the "02" numbers. but even is /02/ was in the formula bar when i tried to replace "02" it should replace, but it doesn't. "Jim Rech" wrote: The "-" parts of your "-02-" may just be due to the date formatting rather than what is "really" in the cell. When you select such a cell what is in the formula bar? Whatever is up there ("/2/" for me) is what you can find and replace on. -- Jim "Xaraam" wrote in message ... | Hey! | | I'm kind of dummie in VBA. I usually just work with it to create macros. | I've a important challenge now, that is: using an excel sheet with, average, | 20000 cells, I need to replace a part of the cell content several times (i.e. | if my cell is 01-02-2006, the "02" has to be replaced by "MA"). | I'm using the VB6 version and excel 2003. | | i've tried this code: | sub replace() | worksheets("book1").select | Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ | SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ | ReplaceFormat:=False | end sub() | | the cells are formatted to date. | | i've just noticed that the code does work but sometimes, i.e. if i want to | replace the content "-02-" to "-MA-", in all my attempts it just worked once. | But if i replace "2006" to "-MA-", it work all the time and the replace | cells are something like this: "01/02/-MA-", from the previous: "01-02-2006". | Even if i replace "0" to MA on "01-02-2006" the result will be: | "1/2/2-MA--MA-6" | | BUt even after all this test and puting the cells on the original values i | try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a | error | message. | | Can you help me out here?..thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell €“ really need you help
Does your code really have brackets "( )" after the End Sub.
That looks like a problem to me. Tom "Xaraam" wrote: Hey! Im kind of dummie in VBA. I usually just work with it to create macros. Ive a important challenge now, that is: using an excel sheet with, average, 20000 cells, I need to replace a part of the cell content several times (i.e. if my cell is 01-02-2006, the €œ02€ has to be replaced by €œMA€). Im using the VB6 version and excel 2003. i've tried this code: sub replace() worksheets("book1").select Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ ReplaceFormat:=False end sub() the cells are formatted to date. i've just noticed that the code does work but sometimes, i.e. if i want to replace the content "-02-" to "-MA-", in all my attempts it just worked once. But if i replace "2006" to "-MA-", it work all the time and the replace cells are something like this: "01/02/-MA-", from the previous: "01-02-2006". Even if i replace "0" to MA on "01-02-2006" the result will be: "1/2/2-MA--MA-6" BUt even after all this test and puting the cells on the original values i try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a error message. Can you help me out here?..thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell €“ really need you help
I think the problem you are having is those entries are true Excel dates,
not text. If that is the case, then what you see is not really what is in the cell. You can see that easy enough by selecting one of the cells and changing its format to General. If you had selected the cell with 01-02-2006 in it, you would now see 38719. That is because Excel stores the date portion of a date as the number of days since 12/31/1899 (hence, 1 is January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will probably have to do something like this to accomplish what you want... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Month(C.Value) & "-MA-" & Year(C.Value) End If End If Next End Sub Rick "Xaraam" wrote in message ... Hey! Im kind of dummie in VBA. I usually just work with it to create macros. Ive a important challenge now, that is: using an excel sheet with, average, 20000 cells, I need to replace a part of the cell content several times (i.e. if my cell is 01-02-2006, the €œ02€ has to be replaced by €œMA€). Im using the VB6 version and excel 2003. i've tried this code: sub replace() worksheets("book1").select Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ ReplaceFormat:=False end sub() the cells are formatted to date. i've just noticed that the code does work but sometimes, i.e. if i want to replace the content "-02-" to "-MA-", in all my attempts it just worked once. But if i replace "2006" to "-MA-", it work all the time and the replace cells are something like this: "01/02/-MA-", from the previous: "01-02-2006". Even if i replace "0" to MA on "01-02-2006" the result will be: "1/2/2-MA--MA-6" BUt even after all this test and puting the cells on the original values i try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a error message. Can you help me out here?..thanks in advance. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
I think you're at least partially right, Rick. But if Excel is really
looking at the serial number in the cells why can you replace the year portion of the date? Anyway, by changing my regional settings date I reproduced the problem. This is a variation for your macro that preserves leading zeros and is regional settings agnostic: Sub Replace02WithMA2() Dim Cell As Range Dim CellString As String For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers) CellString = Cell.Value Cell.Value = Replace(CellString, "-02-", "-MD-") Next End Sub -- Jim "Rick Rothstein (MVP - VB)" wrote in message ... |I think the problem you are having is those entries are true Excel dates, | not text. If that is the case, then what you see is not really what is in | the cell. You can see that easy enough by selecting one of the cells and | changing its format to General. If you had selected the cell with 01-02-2006 | in it, you would now see 38719. That is because Excel stores the date | portion of a date as the number of days since 12/31/1899 (hence, 1 is | January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will | probably have to do something like this to accomplish what you want... | | Sub Replace02WithMA() | Dim C As Range | For Each C In Worksheets("Sheet2").UsedRange | If IsDate(C.Value) Then | If Day(C.Value) = 2 Then | C.Value = Month(C.Value) & "-MA-" & Year(C.Value) | End If | End If | Next | End Sub | | Rick | | | "Xaraam" wrote in message | ... | Hey! | | I'm kind of dummie in VBA. I usually just work with it to create macros. | I've a important challenge now, that is: using an excel sheet with, | average, | 20000 cells, I need to replace a part of the cell content several times | (i.e. | if my cell is 01-02-2006, the "02" has to be replaced by "MA"). | I'm using the VB6 version and excel 2003. | | i've tried this code: | sub replace() | worksheets("book1").select | Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ | SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ | ReplaceFormat:=False | end sub() | | the cells are formatted to date. | | i've just noticed that the code does work but sometimes, i.e. if i want to | replace the content "-02-" to "-MA-", in all my attempts it just worked | once. | But if i replace "2006" to "-MA-", it work all the time and the replace | cells are something like this: "01/02/-MA-", from the previous: | "01-02-2006". | Even if i replace "0" to MA on "01-02-2006" the result will be: | "1/2/2-MA--MA-6" | | BUt even after all this test and puting the cells on the original values i | try to do the "-02-" to "-MA-" replacement and nothing happens, nor even a | error | message. | | Can you help me out here?..thanks in advance. | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
Upon further review I believe this is the ole "Excel operates with US
settings when a macro is running" problem. The OP I believe is not using US settings. So his macro uses: Replace(CellString, "-02-", "-MD-") And it hits a cell that 'really' has 08-02-2006 in it, which is 8-Feb-2006 with UK settings. Excel, being in US mode, sees this cell as having 2/8/2006 in it so there is no match. But if you change the macro to Replace(CellString, "2/", "-MD-") Excel does find that (the first 2 characters in the cell when viewed with US settings) and changes the cell to -MA-8/2006. Not too helpful. So, short of changing to US settings, I think the OP should use sub Replace02WithMA2 I posted. -- Jim "Jim Rech" wrote in message ... |I think you're at least partially right, Rick. But if Excel is really | looking at the serial number in the cells why can you replace the year | portion of the date? | | Anyway, by changing my regional settings date I reproduced the problem. | This is a variation for your macro that preserves leading zeros and is | regional settings agnostic: | | Sub Replace02WithMA2() | Dim Cell As Range | Dim CellString As String | For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers) | CellString = Cell.Value | Cell.Value = Replace(CellString, "-02-", "-MD-") | Next | End Sub | | | -- | Jim | "Rick Rothstein (MVP - VB)" wrote in | message ... ||I think the problem you are having is those entries are true Excel dates, || not text. If that is the case, then what you see is not really what is in || the cell. You can see that easy enough by selecting one of the cells and || changing its format to General. If you had selected the cell with | 01-02-2006 || in it, you would now see 38719. That is because Excel stores the date || portion of a date as the number of days since 12/31/1899 (hence, 1 is || January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will || probably have to do something like this to accomplish what you want... || || Sub Replace02WithMA() || Dim C As Range || For Each C In Worksheets("Sheet2").UsedRange || If IsDate(C.Value) Then || If Day(C.Value) = 2 Then || C.Value = Month(C.Value) & "-MA-" & Year(C.Value) || End If || End If || Next || End Sub || || Rick || || || "Xaraam" wrote in message || ... || Hey! || || I'm kind of dummie in VBA. I usually just work with it to create macros. || I've a important challenge now, that is: using an excel sheet with, || average, || 20000 cells, I need to replace a part of the cell content several times || (i.e. || if my cell is 01-02-2006, the "02" has to be replaced by "MA"). || I'm using the VB6 version and excel 2003. || || i've tried this code: || sub replace() || worksheets("book1").select || Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ || SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ || ReplaceFormat:=False || end sub() || || the cells are formatted to date. || || i've just noticed that the code does work but sometimes, i.e. if i want | to || replace the content "-02-" to "-MA-", in all my attempts it just worked || once. || But if i replace "2006" to "-MA-", it work all the time and the replace || cells are something like this: "01/02/-MA-", from the previous: || "01-02-2006". || Even if i replace "0" to MA on "01-02-2006" the result will be: || "1/2/2-MA--MA-6" || || BUt even after all this test and puting the cells on the original values | i || try to do the "-02-" to "-MA-" replacement and nothing happens, nor even | a || error || message. || || Can you help me out here?..thanks in advance. || | | |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
That sounds reasonable (thankfully, having worked in the US for my entire
working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick "Jim Rech" wrote in message ... Upon further review I believe this is the ole "Excel operates with US settings when a macro is running" problem. The OP I believe is not using US settings. So his macro uses: Replace(CellString, "-02-", "-MD-") And it hits a cell that 'really' has 08-02-2006 in it, which is 8-Feb-2006 with UK settings. Excel, being in US mode, sees this cell as having 2/8/2006 in it so there is no match. But if you change the macro to Replace(CellString, "2/", "-MD-") Excel does find that (the first 2 characters in the cell when viewed with US settings) and changes the cell to -MA-8/2006. Not too helpful. So, short of changing to US settings, I think the OP should use sub Replace02WithMA2 I posted. -- Jim "Jim Rech" wrote in message ... |I think you're at least partially right, Rick. But if Excel is really | looking at the serial number in the cells why can you replace the year | portion of the date? | | Anyway, by changing my regional settings date I reproduced the problem. | This is a variation for your macro that preserves leading zeros and is | regional settings agnostic: | | Sub Replace02WithMA2() | Dim Cell As Range | Dim CellString As String | For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers) | CellString = Cell.Value | Cell.Value = Replace(CellString, "-02-", "-MD-") | Next | End Sub | | | -- | Jim | "Rick Rothstein (MVP - VB)" wrote in | message ... ||I think the problem you are having is those entries are true Excel dates, || not text. If that is the case, then what you see is not really what is in || the cell. You can see that easy enough by selecting one of the cells and || changing its format to General. If you had selected the cell with | 01-02-2006 || in it, you would now see 38719. That is because Excel stores the date || portion of a date as the number of days since 12/31/1899 (hence, 1 is || January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will || probably have to do something like this to accomplish what you want... || || Sub Replace02WithMA() || Dim C As Range || For Each C In Worksheets("Sheet2").UsedRange || If IsDate(C.Value) Then || If Day(C.Value) = 2 Then || C.Value = Month(C.Value) & "-MA-" & Year(C.Value) || End If || End If || Next || End Sub || || Rick || || || "Xaraam" wrote in message || ... || Hey! || || I'm kind of dummie in VBA. I usually just work with it to create macros. || I've a important challenge now, that is: using an excel sheet with, || average, || 20000 cells, I need to replace a part of the cell content several times || (i.e. || if my cell is 01-02-2006, the "02" has to be replaced by "MA"). || I'm using the VB6 version and excel 2003. || || i've tried this code: || sub replace() || worksheets("book1").select || Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ || SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ || ReplaceFormat:=False || end sub() || || the cells are formatted to date. || || i've just noticed that the code does work but sometimes, i.e. if i want | to || replace the content "-02-" to "-MA-", in all my attempts it just worked || once. || But if i replace "2006" to "-MA-", it work all the time and the replace || cells are something like this: "01/02/-MA-", from the previous: || "01-02-2006". || Even if i replace "0" to MA on "01-02-2006" the result will be: || "1/2/2-MA--MA-6" || || BUt even after all this test and puting the cells on the original values | i || try to do the "-02-" to "-MA-" replacement and nothing happens, nor even | a || error || message. || || Can you help me out here?..thanks in advance. || | | |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
Hey Rick!!
it works perfectly!!..thank you a lot!!..the only change i'd to make was: If IsDate(C.Value) Then If Month(C.Value) = 2 Then C.Value = Format(C.Value, "dd-\M\A-yyyy") coz my format is : "dd-mm-yyyy". all my date is on that format, more used here in europe. The code also work without the "\" or even if like this: C.Value = Format(C.Value, "dd-\MA\-yyyy") Just clarify me something, what's the porpuse of "usedrange" and with do u use "c.value" and not just "c"? "Rick Rothstein (MVP - VB)" wrote: That sounds reasonable (thankfully, having worked in the US for my entire working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
hey Jim!
your code works almost perfectly, probably once i wasn't clear about my date format you wouldn't guess i was using the "dd-mm-yyyy" format. the problem on your code is the with the exception of the cell that "mm" corresponds to "02" it swaps the "dd" value for the "mm" value. i-e. if the original code is: 09-04-2006, after running the code : 04-09-2008. however this happens only until the "dd" value reachs "12". thank you very much, anyway, for your code and effort!! "Jim Rech" wrote: Upon further review I believe this is the ole "Excel operates with US settings when a macro is running" problem. The OP I believe is not using US settings. So his macro uses: Replace(CellString, "-02-", "-MD-") And it hits a cell that 'really' has 08-02-2006 in it, which is 8-Feb-2006 with UK settings. Excel, being in US mode, sees this cell as having 2/8/2006 in it so there is no match. But if you change the macro to Replace(CellString, "2/", "-MD-") Excel does find that (the first 2 characters in the cell when viewed with US settings) and changes the cell to -MA-8/2006. Not too helpful. So, short of changing to US settings, I think the OP should use sub Replace02WithMA2 I posted. -- Jim "Jim Rech" wrote in message ... |I think you're at least partially right, Rick. But if Excel is really | looking at the serial number in the cells why can you replace the year | portion of the date? | | Anyway, by changing my regional settings date I reproduced the problem. | This is a variation for your macro that preserves leading zeros and is | regional settings agnostic: | | Sub Replace02WithMA2() | Dim Cell As Range | Dim CellString As String | For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers) | CellString = Cell.Value | Cell.Value = Replace(CellString, "-02-", "-MD-") | Next | End Sub | | | -- | Jim |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
The UsedRange is an automatically tracked range by Excel and it covers the
maximum extent of cells ever put into use on the worksheet. Note I said "ever put into use"... one would have hoped UsedRange covered the maximum extent of cells currently being used but, alas, it doesn't. If you used a cell outside of your current ranged of used cells and later on deleted it, UsedRange still thinks that cell is in use... UsedRange is not supposed to reset itself smaller. Now I am sure I seen it do that (set itself smaller, but I have also seen it not do so... I have not been able to divine the rule behind it). Anyway, using UsedRange, under a normally maintained worksheet, means the For-Each loop can iterate a smaller-than-the-whole-worksheet range and still guarantee it will 'hit' every in-use cell on your worksheet. As for why I use C.Value instead of C... personal preference. A range is an object and objects usually have a default property. A default property means if you don't specify it, VB will assume the default property was intended and automatically reference it. That is what happens when you use C by itself... VB assumes C.Value. The reason I don't like relying on default properties is that without specify any property, the object name looks like a simple variable name making it harder to maintain or modify in the future (when all the stuff that was fresh in mind when you first created your code has long since left your memory)... always attaching a property (or method) reference alerts me that I'm looking at an object, not a variable. Rick "Xaraam" wrote in message ... Hey Rick!! it works perfectly!!..thank you a lot!!..the only change i'd to make was: If IsDate(C.Value) Then If Month(C.Value) = 2 Then C.Value = Format(C.Value, "dd-\M\A-yyyy") coz my format is : "dd-mm-yyyy". all my date is on that format, more used here in europe. The code also work without the "\" or even if like this: C.Value = Format(C.Value, "dd-\MA\-yyyy") Just clarify me something, what's the porpuse of "usedrange" and with do u use "c.value" and not just "c"? "Rick Rothstein (MVP - VB)" wrote: That sounds reasonable (thankfully, having worked in the US for my entire working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
As for what controls the setting/resetting of the UsedRange, it looks like
deleting entire columns and/or rows is the key. See the "To programmatically reset the used range" section on this webpage... http://www.contextures.on.ca/xlfaqApp.html Rick "Rick Rothstein (MVP - VB)" wrote in message ... The UsedRange is an automatically tracked range by Excel and it covers the maximum extent of cells ever put into use on the worksheet. Note I said "ever put into use"... one would have hoped UsedRange covered the maximum extent of cells currently being used but, alas, it doesn't. If you used a cell outside of your current ranged of used cells and later on deleted it, UsedRange still thinks that cell is in use... UsedRange is not supposed to reset itself smaller. Now I am sure I seen it do that (set itself smaller, but I have also seen it not do so... I have not been able to divine the rule behind it). Anyway, using UsedRange, under a normally maintained worksheet, means the For-Each loop can iterate a smaller-than-the-whole-worksheet range and still guarantee it will 'hit' every in-use cell on your worksheet. As for why I use C.Value instead of C... personal preference. A range is an object and objects usually have a default property. A default property means if you don't specify it, VB will assume the default property was intended and automatically reference it. That is what happens when you use C by itself... VB assumes C.Value. The reason I don't like relying on default properties is that without specify any property, the object name looks like a simple variable name making it harder to maintain or modify in the future (when all the stuff that was fresh in mind when you first created your code has long since left your memory)... always attaching a property (or method) reference alerts me that I'm looking at an object, not a variable. Rick "Xaraam" wrote in message ... Hey Rick!! it works perfectly!!..thank you a lot!!..the only change i'd to make was: If IsDate(C.Value) Then If Month(C.Value) = 2 Then C.Value = Format(C.Value, "dd-\M\A-yyyy") coz my format is : "dd-mm-yyyy". all my date is on that format, more used here in europe. The code also work without the "\" or even if like this: C.Value = Format(C.Value, "dd-\MA\-yyyy") Just clarify me something, what's the porpuse of "usedrange" and with do u use "c.value" and not just "c"? "Rick Rothstein (MVP - VB)" wrote: That sounds reasonable (thankfully, having worked in the US for my entire working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
it seems like such a nightmare to me
Yes. Stephen Bullen devoted a lot of ink to it in the Excel VBA books he did with Rob Bovey and John Green. Too bad MS couldn't have used an approach that didn't make it so much harder for non-US users. -- Jim "Rick Rothstein (MVP - VB)" wrote in message ... That sounds reasonable (thankfully, having worked in the US for my entire working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick "Jim Rech" wrote in message ... Upon further review I believe this is the ole "Excel operates with US settings when a macro is running" problem. The OP I believe is not using US settings. So his macro uses: Replace(CellString, "-02-", "-MD-") And it hits a cell that 'really' has 08-02-2006 in it, which is 8-Feb-2006 with UK settings. Excel, being in US mode, sees this cell as having 2/8/2006 in it so there is no match. But if you change the macro to Replace(CellString, "2/", "-MD-") Excel does find that (the first 2 characters in the cell when viewed with US settings) and changes the cell to -MA-8/2006. Not too helpful. So, short of changing to US settings, I think the OP should use sub Replace02WithMA2 I posted. -- Jim "Jim Rech" wrote in message ... |I think you're at least partially right, Rick. But if Excel is really | looking at the serial number in the cells why can you replace the year | portion of the date? | | Anyway, by changing my regional settings date I reproduced the problem. | This is a variation for your macro that preserves leading zeros and is | regional settings agnostic: | | Sub Replace02WithMA2() | Dim Cell As Range | Dim CellString As String | For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlNumbers) | CellString = Cell.Value | Cell.Value = Replace(CellString, "-02-", "-MD-") | Next | End Sub | | | -- | Jim | "Rick Rothstein (MVP - VB)" wrote in | message ... ||I think the problem you are having is those entries are true Excel dates, || not text. If that is the case, then what you see is not really what is in || the cell. You can see that easy enough by selecting one of the cells and || changing its format to General. If you had selected the cell with | 01-02-2006 || in it, you would now see 38719. That is because Excel stores the date || portion of a date as the number of days since 12/31/1899 (hence, 1 is || January 1, 1900); January 2, 2006 is 38719 days past 12/31/1899. You will || probably have to do something like this to accomplish what you want... || || Sub Replace02WithMA() || Dim C As Range || For Each C In Worksheets("Sheet2").UsedRange || If IsDate(C.Value) Then || If Day(C.Value) = 2 Then || C.Value = Month(C.Value) & "-MA-" & Year(C.Value) || End If || End If || Next || End Sub || || Rick || || || "Xaraam" wrote in message || ... || Hey! || || I'm kind of dummie in VBA. I usually just work with it to create macros. || I've a important challenge now, that is: using an excel sheet with, || average, || 20000 cells, I need to replace a part of the cell content several times || (i.e. || if my cell is 01-02-2006, the "02" has to be replaced by "MA"). || I'm using the VB6 version and excel 2003. || || i've tried this code: || sub replace() || worksheets("book1").select || Cells.Replace What:="-02-", Replacement:="-MA-", LookAt:=xlPart, _ || SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,_ || ReplaceFormat:=False || end sub() || || the cells are formatted to date. || || i've just noticed that the code does work but sometimes, i.e. if i want | to || replace the content "-02-" to "-MA-", in all my attempts it just worked || once. || But if i replace "2006" to "-MA-", it work all the time and the replace || cells are something like this: "01/02/-MA-", from the previous: || "01-02-2006". || Even if i replace "0" to MA on "01-02-2006" the result will be: || "1/2/2-MA--MA-6" || || BUt even after all this test and puting the cells on the original values | i || try to do the "-02-" to "-MA-" replacement and nothing happens, nor even | a || error || message. || || Can you help me out here?..thanks in advance. || | | |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
woh!..what an explanation!..questions completely vanished!:)
once again thanks for your help and explanations.. "Rick Rothstein (MVP - VB)" wrote: The UsedRange is an automatically tracked range by Excel and it covers the maximum extent of cells ever put into use on the worksheet. Note I said "ever put into use"... one would have hoped UsedRange covered the maximum extent of cells currently being used but, alas, it doesn't. If you used a cell outside of your current ranged of used cells and later on deleted it, UsedRange still thinks that cell is in use... UsedRange is not supposed to reset itself smaller. Now I am sure I seen it do that (set itself smaller, but I have also seen it not do so... I have not been able to divine the rule behind it). Anyway, using UsedRange, under a normally maintained worksheet, means the For-Each loop can iterate a smaller-than-the-whole-worksheet range and still guarantee it will 'hit' every in-use cell on your worksheet. As for why I use C.Value instead of C... personal preference. A range is an object and objects usually have a default property. A default property means if you don't specify it, VB will assume the default property was intended and automatically reference it. That is what happens when you use C by itself... VB assumes C.Value. The reason I don't like relying on default properties is that without specify any property, the object name looks like a simple variable name making it harder to maintain or modify in the future (when all the stuff that was fresh in mind when you first created your code has long since left your memory)... always attaching a property (or method) reference alerts me that I'm looking at an object, not a variable. Rick "Xaraam" wrote in message ... Hey Rick!! it works perfectly!!..thank you a lot!!..the only change i'd to make was: If IsDate(C.Value) Then If Month(C.Value) = 2 Then C.Value = Format(C.Value, "dd-\M\A-yyyy") coz my format is : "dd-mm-yyyy". all my date is on that format, more used here in europe. The code also work without the "\" or even if like this: C.Value = Format(C.Value, "dd-\MA\-yyyy") Just clarify me something, what's the porpuse of "usedrange" and with do u use "c.value" and not just "c"? "Rick Rothstein (MVP - VB)" wrote: That sounds reasonable (thankfully, having worked in the US for my entire working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
Rick,
now i have another challenge that probably you can help: i've got 2 row of values, 1 with those modified date and another one with corresponding integer values. I want to turn them into a chart line type, with the date on the x axis and the integer values on the y axis. any idea how to do that on VBA? "Rick Rothstein (MVP - VB)" wrote: As for what controls the setting/resetting of the UsedRange, it looks like deleting entire columns and/or rows is the key. See the "To programmatically reset the used range" section on this webpage... http://www.contextures.on.ca/xlfaqApp.html Rick "Rick Rothstein (MVP - VB)" wrote in message ... The UsedRange is an automatically tracked range by Excel and it covers the maximum extent of cells ever put into use on the worksheet. Note I said "ever put into use"... one would have hoped UsedRange covered the maximum extent of cells currently being used but, alas, it doesn't. If you used a cell outside of your current ranged of used cells and later on deleted it, UsedRange still thinks that cell is in use... UsedRange is not supposed to reset itself smaller. Now I am sure I seen it do that (set itself smaller, but I have also seen it not do so... I have not been able to divine the rule behind it). Anyway, using UsedRange, under a normally maintained worksheet, means the For-Each loop can iterate a smaller-than-the-whole-worksheet range and still guarantee it will 'hit' every in-use cell on your worksheet. As for why I use C.Value instead of C... personal preference. A range is an object and objects usually have a default property. A default property means if you don't specify it, VB will assume the default property was intended and automatically reference it. That is what happens when you use C by itself... VB assumes C.Value. The reason I don't like relying on default properties is that without specify any property, the object name looks like a simple variable name making it harder to maintain or modify in the future (when all the stuff that was fresh in mind when you first created your code has long since left your memory)... always attaching a property (or method) reference alerts me that I'm looking at an object, not a variable. Rick "Xaraam" wrote in message ... Hey Rick!! it works perfectly!!..thank you a lot!!..the only change i'd to make was: If IsDate(C.Value) Then If Month(C.Value) = 2 Then C.Value = Format(C.Value, "dd-\M\A-yyyy") coz my format is : "dd-mm-yyyy". all my date is on that format, more used here in europe. The code also work without the "\" or even if like this: C.Value = Format(C.Value, "dd-\MA\-yyyy") Just clarify me something, what's the porpuse of "usedrange" and with do u use "c.value" and not just "c"? "Rick Rothstein (MVP - VB)" wrote: That sounds reasonable (thankfully, having worked in the US for my entire working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
I'm going to suggest you post this as new question to the newsgroup... I
have not very much (read that as almost nothing) with charting, so I am not the right person to seek advice from on them. Posting the question new should expose it to many who are more qualified to answer it than I am. Rick "Xaraam" wrote in message ... Rick, now i have another challenge that probably you can help: i've got 2 row of values, 1 with those modified date and another one with corresponding integer values. I want to turn them into a chart line type, with the date on the x axis and the integer values on the y axis. any idea how to do that on VBA? "Rick Rothstein (MVP - VB)" wrote: As for what controls the setting/resetting of the UsedRange, it looks like deleting entire columns and/or rows is the key. See the "To programmatically reset the used range" section on this webpage... http://www.contextures.on.ca/xlfaqApp.html Rick "Rick Rothstein (MVP - VB)" wrote in message ... The UsedRange is an automatically tracked range by Excel and it covers the maximum extent of cells ever put into use on the worksheet. Note I said "ever put into use"... one would have hoped UsedRange covered the maximum extent of cells currently being used but, alas, it doesn't. If you used a cell outside of your current ranged of used cells and later on deleted it, UsedRange still thinks that cell is in use... UsedRange is not supposed to reset itself smaller. Now I am sure I seen it do that (set itself smaller, but I have also seen it not do so... I have not been able to divine the rule behind it). Anyway, using UsedRange, under a normally maintained worksheet, means the For-Each loop can iterate a smaller-than-the-whole-worksheet range and still guarantee it will 'hit' every in-use cell on your worksheet. As for why I use C.Value instead of C... personal preference. A range is an object and objects usually have a default property. A default property means if you don't specify it, VB will assume the default property was intended and automatically reference it. That is what happens when you use C by itself... VB assumes C.Value. The reason I don't like relying on default properties is that without specify any property, the object name looks like a simple variable name making it harder to maintain or modify in the future (when all the stuff that was fresh in mind when you first created your code has long since left your memory)... always attaching a property (or method) reference alerts me that I'm looking at an object, not a variable. Rick "Xaraam" wrote in message ... Hey Rick!! it works perfectly!!..thank you a lot!!..the only change i'd to make was: If IsDate(C.Value) Then If Month(C.Value) = 2 Then C.Value = Format(C.Value, "dd-\M\A-yyyy") coz my format is : "dd-mm-yyyy". all my date is on that format, more used here in europe. The code also work without the "\" or even if like this: C.Value = Format(C.Value, "dd-\MA\-yyyy") Just clarify me something, what's the porpuse of "usedrange" and with do u use "c.value" and not just "c"? "Rick Rothstein (MVP - VB)" wrote: That sounds reasonable (thankfully, having worked in the US for my entire working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
ok..no problem..thanks anyways.
"Rick Rothstein (MVP - VB)" wrote: I'm going to suggest you post this as new question to the newsgroup... I have not very much (read that as almost nothing) with charting, so I am not the right person to seek advice from on them. Posting the question new should expose it to many who are more qualified to answer it than I am. Rick "Xaraam" wrote in message ... Rick, now i have another challenge that probably you can help: i've got 2 row of values, 1 with those modified date and another one with corresponding integer values. I want to turn them into a chart line type, with the date on the x axis and the integer values on the y axis. any idea how to do that on VBA? "Rick Rothstein (MVP - VB)" wrote: As for what controls the setting/resetting of the UsedRange, it looks like deleting entire columns and/or rows is the key. See the "To programmatically reset the used range" section on this webpage... http://www.contextures.on.ca/xlfaqApp.html Rick "Rick Rothstein (MVP - VB)" wrote in message ... The UsedRange is an automatically tracked range by Excel and it covers the maximum extent of cells ever put into use on the worksheet. Note I said "ever put into use"... one would have hoped UsedRange covered the maximum extent of cells currently being used but, alas, it doesn't. If you used a cell outside of your current ranged of used cells and later on deleted it, UsedRange still thinks that cell is in use... UsedRange is not supposed to reset itself smaller. Now I am sure I seen it do that (set itself smaller, but I have also seen it not do so... I have not been able to divine the rule behind it). Anyway, using UsedRange, under a normally maintained worksheet, means the For-Each loop can iterate a smaller-than-the-whole-worksheet range and still guarantee it will 'hit' every in-use cell on your worksheet. As for why I use C.Value instead of C... personal preference. A range is an object and objects usually have a default property. A default property means if you don't specify it, VB will assume the default property was intended and automatically reference it. That is what happens when you use C by itself... VB assumes C.Value. The reason I don't like relying on default properties is that without specify any property, the object name looks like a simple variable name making it harder to maintain or modify in the future (when all the stuff that was fresh in mind when you first created your code has long since left your memory)... always attaching a property (or method) reference alerts me that I'm looking at an object, not a variable. Rick "Xaraam" wrote in message ... Hey Rick!! it works perfectly!!..thank you a lot!!..the only change i'd to make was: If IsDate(C.Value) Then If Month(C.Value) = 2 Then C.Value = Format(C.Value, "dd-\M\A-yyyy") coz my format is : "dd-mm-yyyy". all my date is on that format, more used here in europe. The code also work without the "\" or even if like this: C.Value = Format(C.Value, "dd-\MA\-yyyy") Just clarify me something, what's the porpuse of "usedrange" and with do u use "c.value" and not just "c"? "Rick Rothstein (MVP - VB)" wrote: That sounds reasonable (thankfully, having worked in the US for my entire working career, I never had to deal with international issues in any of my programs... it seems like such a nightmare to me<g). As for my macro not having preserved leading zeroes (your original comment to me), that can be easily fixed... Sub Replace02WithMA() Dim C As Range For Each C In Worksheets("Sheet2").UsedRange If IsDate(C.Value) Then If Day(C.Value) = 2 Then C.Value = Format(C.Value, "mm-\M\A-yyyy") End If End If Next End Sub I'm not entirely sure which of our macros is physically replacing the smallest number of cell values. Rick |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
Rick
I stumbled across this UDF from Bob Flanagan which ignores those once filled then cleared cells. Function RangeToUse(anySheet As Worksheet) As Range 'this function returns the range from cells A1 to cell which is the 'intersection of the last row with an entry and the last column with an 'entry. Dim I As Integer, c As Integer, r As Integer With anySheet.UsedRange I = .Cells(.Cells.Count).Column + 1 For c = I To 1 Step -1 If Application.CountA(anySheet.Columns(c)) 0 _ Then Exit For Next I = .Cells(.Cells.Count).Row + 1 For r = I To 1 Step -1 If Application.CountA(anySheet.Rows(r)) 0 Then _ Exit For Next End With With anySheet Set RangeToUse = .Range(.Cells(1, 1), .Cells(r, c)) End With End Function Then a statement like Set rng = RangeToUse(ActiveSheet) Gord On Sat, 2 Aug 2008 17:28:02 -0700, Xaraam wrote: As for what controls the setting/resetting of the UsedRange, it looks like deleting entire columns and/or rows is the key. See the "To programmatically reset the used range" section on this webpage... |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
That reminded me of these two functions that I dummied up awhile ago (one
for determining the maximum row in use and the other for determining the maximum column in use), but I don't think I ever posted them to any newsgroups before. If you don't specify a worksheet in the first (optional) argument, then the active sheet is used. The second optional argument is the interesting one... it lets you determine whether to include hidden rows or columns when determining the maximum row and column that are in use; that is, if a hidden row or column contains the maximum row or column, it will be ignored unless the second argument is set to True. This allows you to get the maximum row or column for what you see on the worksheet rather than for what what any hidden data would return. I wasn't sure which would be the most logical default for this second argument, so I chose not factor in hidden rows or columns (that is, the functions return the maximum row and column for only the visible data); if desired, this can be easily changed in the declaration headers for each function. I hope you and others will find this of some use... Function MaxRowInUse(Optional WS As Worksheet, Optional _ FactorInHiddenRows As Boolean = False) As Long Dim X As Long Dim LastRow As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Columns.Count If Not (Not FactorInHiddenRows And Columns(X).Width = 0) Then LastRow = .Cells(.Rows.Count, X).End(xlUp).Row If LastRow MaxRowInUse Then MaxRowInUse = LastRow End If Next End With End Function Function MaxColumnInUse(Optional WS As Worksheet, Optional _ FactorInHiddenColumns As Boolean = False) As Long Dim X As Long Dim LastColumn As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Rows.Count If Not (Not FactorInHiddenColumns And Rows(X).Height = 0) Then LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column If LastColumn MaxColumnInUse Then MaxColumnInUse = LastColumn End If Next End With End Function Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Rick I stumbled across this UDF from Bob Flanagan which ignores those once filled then cleared cells. Function RangeToUse(anySheet As Worksheet) As Range 'this function returns the range from cells A1 to cell which is the 'intersection of the last row with an entry and the last column with an 'entry. Dim I As Integer, c As Integer, r As Integer With anySheet.UsedRange I = .Cells(.Cells.Count).Column + 1 For c = I To 1 Step -1 If Application.CountA(anySheet.Columns(c)) 0 _ Then Exit For Next I = .Cells(.Cells.Count).Row + 1 For r = I To 1 Step -1 If Application.CountA(anySheet.Rows(r)) 0 Then _ Exit For Next End With With anySheet Set RangeToUse = .Range(.Cells(1, 1), .Cells(r, c)) End With End Function Then a statement like Set rng = RangeToUse(ActiveSheet) Gord On Sat, 2 Aug 2008 17:28:02 -0700, Xaraam wrote: As for what controls the setting/resetting of the UsedRange, it looks like deleting entire columns and/or rows is the key. See the "To programmatically reset the used range" section on this webpage... |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and Excell - really need you help
In looking over my code again, I guess it would be more Excel-like if I used
the Hidden properties of the Columns and Rows in my tests rather than test for their being "equal to zero" (even though there is no practical difference between doing either)... Function MaxRowInUse(Optional WS As Worksheet, Optional _ FactorInHiddenRows As Boolean = False) As Long Dim X As Long Dim LastRow As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Columns.Count If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then LastRow = .Cells(.Rows.Count, X).End(xlUp).Row If LastRow MaxRowInUse Then MaxRowInUse = LastRow End If Next End With End Function Function MaxColumnInUse(Optional WS As Worksheet, Optional _ FactorInHiddenColumns As Boolean = False) As Long Dim X As Long Dim LastColumn As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Rows.Count If Not (Not FactorInHiddenColumns And Rows(X).Hidden) Then LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column If LastColumn MaxColumnInUse Then MaxColumnInUse = LastColumn End If Next End With End Function Rick "Rick Rothstein (MVP - VB)" wrote in message ... That reminded me of these two functions that I dummied up awhile ago (one for determining the maximum row in use and the other for determining the maximum column in use), but I don't think I ever posted them to any newsgroups before. If you don't specify a worksheet in the first (optional) argument, then the active sheet is used. The second optional argument is the interesting one... it lets you determine whether to include hidden rows or columns when determining the maximum row and column that are in use; that is, if a hidden row or column contains the maximum row or column, it will be ignored unless the second argument is set to True. This allows you to get the maximum row or column for what you see on the worksheet rather than for what what any hidden data would return. I wasn't sure which would be the most logical default for this second argument, so I chose not factor in hidden rows or columns (that is, the functions return the maximum row and column for only the visible data); if desired, this can be easily changed in the declaration headers for each function. I hope you and others will find this of some use... Function MaxRowInUse(Optional WS As Worksheet, Optional _ FactorInHiddenRows As Boolean = False) As Long Dim X As Long Dim LastRow As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Columns.Count If Not (Not FactorInHiddenRows And Columns(X).Width = 0) Then LastRow = .Cells(.Rows.Count, X).End(xlUp).Row If LastRow MaxRowInUse Then MaxRowInUse = LastRow End If Next End With End Function Function MaxColumnInUse(Optional WS As Worksheet, Optional _ FactorInHiddenColumns As Boolean = False) As Long Dim X As Long Dim LastColumn As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Rows.Count If Not (Not FactorInHiddenColumns And Rows(X).Height = 0) Then LastColumn = .Cells(X, .Columns.Count).End(xlToLeft).Column If LastColumn MaxColumnInUse Then MaxColumnInUse = LastColumn End If Next End With End Function Rick "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Rick I stumbled across this UDF from Bob Flanagan which ignores those once filled then cleared cells. Function RangeToUse(anySheet As Worksheet) As Range 'this function returns the range from cells A1 to cell which is the 'intersection of the last row with an entry and the last column with an 'entry. Dim I As Integer, c As Integer, r As Integer With anySheet.UsedRange I = .Cells(.Cells.Count).Column + 1 For c = I To 1 Step -1 If Application.CountA(anySheet.Columns(c)) 0 _ Then Exit For Next I = .Cells(.Cells.Count).Row + 1 For r = I To 1 Step -1 If Application.CountA(anySheet.Rows(r)) 0 Then _ Exit For Next End With With anySheet Set RangeToUse = .Range(.Cells(1, 1), .Cells(r, c)) End With End Function Then a statement like Set rng = RangeToUse(ActiveSheet) Gord On Sat, 2 Aug 2008 17:28:02 -0700, Xaraam wrote: As for what controls the setting/resetting of the UsedRange, it looks like deleting entire columns and/or rows is the key. See the "To programmatically reset the used range" section on this webpage... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to change the normal excell icon on an excell file? | Excel Worksheet Functions | |||
auto locking of excell workbook (excell 2003) | Excel Discussion (Misc queries) | |||
How to open MS Excell 2007 Sheet in MS Excell 2000??? | New Users to Excel | |||
create a slides show with excell spreadsheets using excell | Charts and Charting in Excel | |||
how do you open an excell email attacment, if I dont have excell | Excel Discussion (Misc queries) |