Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove h
My spreadsheet has 7 Columns of data..A:G. I have monthly sales numbers to
the right of that with the month headers starting in H4. The monthly numbers will have a trailing h with them. The monthly columns may be H6:N50 or H6 to JA 5000. The numbers in H6:...could be one diget to 10 digets. Is there any way to go through this and eliminate the h? thanks in advance, CB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove h
The following code should do what you're after: Code: -------------------- Sub removeH() Range(Cells(6, "H"), Cells(Rows.Count, Columns.Count)).Replace _ What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub -------------------- -- Paul - Paul ------------------------------------------------------------------------ Paul's Profile: 1697 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192578 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove h
you could do this with formulas, but since you're asking in the excel
programming group, here's some code that will do it. This has user manually select the cells they want it to work on. But you could just as easily define a set range... Sub StripRightH() For Each cell In Selection.Cells If cell < "" And UCase(Right(cell, 1)) = "H" Then _ cell.Value = Left(cell, Len(cell) - 1) Next cell End Sub "C" wrote: My spreadsheet has 7 Columns of data..A:G. I have monthly sales numbers to the right of that with the month headers starting in H4. The monthly numbers will have a trailing h with them. The monthly columns may be H6:N50 or H6 to JA 5000. The numbers in H6:...could be one diget to 10 digets. Is there any way to go through this and eliminate the h? thanks in advance, CB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove h
Not sure why my posts don't appear on the actual newsgroups all the time. Anyway.. The following code should do what you're after: Sub removeH() Range(Cells(6, "H"), Cells(Rows.Count, Columns.Count)).Replace _ What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub -- Paul - Paul ------------------------------------------------------------------------ Paul's Profile: 1697 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192578 http://www.thecodecage.com/forumz |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove h
Your first post in this thread showed up 18 minutes ago.
Gord On Fri, 2 Apr 2010 18:46:36 +0000, Paul wrote: Not sure why my posts don't appear on the actual newsgroups all the time. Anyway.. The following code should do what you're after: Sub removeH() Range(Cells(6, "H"), Cells(Rows.Count, Columns.Count)).Replace _ What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove h
This works fine for a selection. Is there another way to do this by asking
for the range from the user, then converting this back to text as the cells see them as a number stored as text. Thanks "B Lynn B" wrote: you could do this with formulas, but since you're asking in the excel programming group, here's some code that will do it. This has user manually select the cells they want it to work on. But you could just as easily define a set range... Sub StripRightH() For Each cell In Selection.Cells If cell < "" And UCase(Right(cell, 1)) = "H" Then _ cell.Value = Left(cell, Len(cell) - 1) Next cell End Sub "C" wrote: My spreadsheet has 7 Columns of data..A:G. I have monthly sales numbers to the right of that with the month headers starting in H4. The monthly numbers will have a trailing h with them. The monthly columns may be H6:N50 or H6 to JA 5000. The numbers in H6:...could be one diget to 10 digets. Is there any way to go through this and eliminate the h? thanks in advance, CB |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove h
Sub removeH()
Range(Cells(6, "H"), Cells(Rows.Count, Columns.Count)).Replace _ What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub "C" wrote: My spreadsheet has 7 Columns of data..A:G. I have monthly sales numbers to the right of that with the month headers starting in H4. The monthly numbers will have a trailing h with them. The monthly columns may be H6:N50 or H6 to JA 5000. The numbers in H6:...could be one diget to 10 digets. Is there any way to go through this and eliminate the h? thanks in advance, CB |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove h
Odd.. I'm monitoring the newsgroups right on Microsoft.com and through codecage.com (where I'm submitting replies), and it takes 40-60 minutes for my replies from codecage.com to appear on the actual newsgroups, and I'm refreshing several times per minute. The posting times when they do appear (adjusted for Pacific - Central time) is 40+ minutes later on average. I just re-posted the same answer by logging directly into the newgroups and it took a few minutes to appear there. (Understandable but still slow.) Darn technology!! Gord Dibben;689107 Wrote: Your first post in this thread showed up 18 minutes ago. Gord On Fri, 2 Apr 2010 18:46:36 +0000, Paul wrote: Not sure why my posts don't appear on the actual newsgroups all the time. Anyway.. The following code should do what you're after: Sub removeH() Range(Cells(6, "H"), Cells(Rows.Count, Columns.Count)).Replace _ What:="h", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub -- Paul - Paul ------------------------------------------------------------------------ Paul's Profile: 1697 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=192578 http://www.thecodecage.com/forumz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i remove an excel link that break link won't remove | Excel Worksheet Functions | |||
Remove add-in - also remove toolbar | Excel Programming | |||
remove convert/extract the number from'12345.56; ie remove ' sign | Excel Worksheet Functions | |||
How do I remove hyperlink if 'remove' option is disabled | New Users to Excel | |||
Is there a way to remove numbers w/o remove formulas | Excel Programming |