| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
newsgroup advice "selecting"
1st post, btw.........
i've read a lot of advice from you wonderful MVP people advising against having a macro "select" cells all the time, for the sake of saving time. i don't understand exactly how you write the code without selecting things............. could you pls. amend this code below so that i can see how you write it without selecting things? (pls. excuse it the code - i'm sure it could be written much more neatly but i'm still learning! like i realize all the "extra" parts of the paste special can be deleted - just haven't got there yet!) thanks very much! :D susan ---------------- Sub UpdateCount() ' ' UpdateCount Macro ' Macro recorded 1/23/2006 by Susan ' ' revised 9/19/2006 to hide "X" worksheet ' Application.ScreenUpdating = False Sheets("X").Visible = True Sheets("X").Select Columns("A:A").Select Selection.ClearContents Range("A1").Select Sheets("FOR SBH ONLY").Select Columns("B:B").Select Selection.Copy Sheets("X").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Application.CutCopyMode = False Selection.ClearContents Cells.Replace What:="contractor", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="inspectors:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="general:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="electric:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="furnace & plumbing:", Replacement:="", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="wells & excavation:", Replacement:="", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="septic:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="sub-s:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Columns("A:A").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("D3").Select ActiveWindow.SelectedSheets.Visible = False Sheets("FOR SBH ONLY").Select Range("A1").Select Application.ScreenUpdating = True End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
newsgroup advice "selecting"
Sub UpdateCount()
' ' UpdateCount Macro ' Macro recorded 1/23/2006 by Susan ' ' revised 9/19/2006 to hide "X" worksheet ' Application.ScreenUpdating = False Sheets("X").Visible = True Sheets("X").Select Columns("A:A").ClearContents Sheets("FOR SBH ONLY").Columns("B:B").Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False .. .. .. and so on hth Greetings Carlo "Susan" wrote: 1st post, btw......... i've read a lot of advice from you wonderful MVP people advising against having a macro "select" cells all the time, for the sake of saving time. i don't understand exactly how you write the code without selecting things............. could you pls. amend this code below so that i can see how you write it without selecting things? (pls. excuse it the code - i'm sure it could be written much more neatly but i'm still learning! like i realize all the "extra" parts of the paste special can be deleted - just haven't got there yet!) thanks very much! :D susan ---------------- Sub UpdateCount() ' ' UpdateCount Macro ' Macro recorded 1/23/2006 by Susan ' ' revised 9/19/2006 to hide "X" worksheet ' Application.ScreenUpdating = False Sheets("X").Visible = True Sheets("X").Select Columns("A:A").Select Selection.ClearContents Range("A1").Select Sheets("FOR SBH ONLY").Select Columns("B:B").Select Selection.Copy Sheets("X").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Application.CutCopyMode = False Selection.ClearContents Cells.Replace What:="contractor", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="inspectors:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="general:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="electric:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="furnace & plumbing:", Replacement:="", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="wells & excavation:", Replacement:="", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="septic:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="sub-s:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Columns("A:A").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("D3").Select ActiveWindow.SelectedSheets.Visible = False Sheets("FOR SBH ONLY").Select Range("A1").Select Application.ScreenUpdating = True End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
newsgroup advice "selecting"
On 20 Sep 2006 04:55:59 -0700, "Susan" wrote:
1st post, btw......... i've read a lot of advice from you wonderful MVP people advising against having a macro "select" cells all the time, for the sake of saving time. i don't understand exactly how you write the code without selecting things............. could you pls. amend this code below so that i can see how you write it without selecting things? (pls. excuse it the code - i'm sure it could be written much more neatly but i'm still learning! like i realize all the "extra" parts of the paste special can be deleted - just haven't got there yet!) thanks very much! :D susan You just reference the cells directly. See below ---------------- Sub UpdateCount() ' ' UpdateCount Macro ' Macro recorded 1/23/2006 by Susan ' ' revised 9/19/2006 to hide "X" worksheet ' Application.ScreenUpdating = False Sheets("X").Visible = True XXX Sheets("X").Select XXX Columns("A:A").Select XXX Selection.ClearContents Sheets("X").Columns("A:A").ClearContents XXX Range("A1").Select XXX Sheets("FOR SBH ONLY").Select XXX Columns("B:B").Select XXX Selection.Copy Sheets("FOR SBH ONLY").Columns("B:B").Copy You should be able to figure the rest out from these examples Sheets("X").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1").Select Application.CutCopyMode = False Selection.ClearContents Cells.Replace What:="contractor", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="inspectors:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="general:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="electric:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="furnace & plumbing:", Replacement:="", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="wells & excavation:", Replacement:="", LookAt:= _ xlPart, SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="septic:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Cells.Replace What:="sub-s:", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Columns("A:A").Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Range("D3").Select ActiveWindow.SelectedSheets.Visible = False Sheets("FOR SBH ONLY").Select Range("A1").Select Application.ScreenUpdating = True End Sub --ron |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
newsgroup advice "selecting"
thanks all so much...... now that i can SEE what you mean versus what i
wrote, i understand what you're saying. thanks! susan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Selecting "Save As" adds "Copy of" to file name- MS Excel 2007 | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Advice needed: "Portable" macros? | Excel Programming | |||
Linear Mode
