The black square in the lower-right corner of the active cell or selection. When the pointer is on the fill handle, the pointer changes from an arrow to a crosshair. Drag the fill handle down or to the right to fill cells with data based on the current selection. Drag it up or to the left to clear the contents of selected cells.
You can insert or delete rows and columns by pressing SHIFT while dragging the fill handle.
The above text was obtained directly from your HELP tooltips, using the Context Sensitive Help button [\?] and pointing to the Fill-Handle.
Press left button on fill-handle: You get a toolbar tip to Drag outside selection to extend series, or fill; drag inside to clear. (#push)
fill handle is missing: If the fill handle is missing go into tools, options, Edit, and check Allow cell drag and drop.
This option also affects ability to move page breaks with the mouse.
The fill-handle is actually optional, if it is missing use Tools|Options|Edit Tab|Check allow drag and drop to restore it's use. [In Excel 2007 click on Office button where File used to be, then click on Options at the bottom, then on Advanced at the left, then Enable fill handle and cell drag-and-drop]
Right-Click fill-handle and drag: Topics include copy from a formula, of numbers dates or other items, linear best-fit trend; growth trend.
The macro equivalent ( SUB ) to double-click.
Dim rng as Range
|
Also check HELP (F1) --> Index --> Fill in a series ...
You can make your own custom series using Tools, Options..., Custom Lists (tab)
To help preselect a range of cells such as before using the fill-handle:
The fill-handle is an option:« -- i.e. "The Fill Handle is missing"
Tools --> Options --> Edit --> (select) Allow cell drag and drop check box is selected.
HELP (F1) --> Answer Wizard --> fill-handle
Some things to note when you want to increment constants. The use of the control key will cause a different result, similar to other usages where the use of SHIFT+ causes the opposite result.
with single cell selections
with multiple cell selections:
- dragging the fill handle of a single cell with a number will replicate down without incrementation.
- Ctrl+ drag will increment a single cell selection with a numeric constant. It will not increment constants in multiple cell selections if there is no increment. i.e. A1:B1 with constants will not increment downward.
- Letters are the opposite. A single cell letter constant will increment through the alphabet with the fill handle.
- Ctrl+ drag fill handle will not increment a single cell letter constant as it is replicated down.
- you must include an increment cell in the direction you fill to get incrementation (both numbers and single letters)
- Holding the Control (Ctrl+) key down will only fill down repeating groups of cells.
Use of RtMouse button to indicate what you want
You can make your own custom lists
- You can specify how you want the filling to be done if use the RtMouse button instead of the LtMouse button when you use the fill handle to drag your selection down. After dragging you will be asked what you want to do.
|| copy values, fill series, fill formats, fill values||
|| fill days, fill weekdays, fill months||
|| Linear trend, Growth Trend, series ... ||
with other choices where applicable.
Tools, Options, Custom Lists
Sun,Mon,Tue,Wed,Thu,Fri,SatEquivalent Examples With the data in cell A3, to be copied down to cell A30:
- Select cell A3
- Point to the fill handle (the black square in the lower right corner of the selection)
- When the pointer changes to a black cross, drag down to A30
- Click in the Name Box, to the left of the Formula Bar
- Type -- A3:A30
- Press the Enter key
- Press Ctrl+D to fill down.
Use of the name box is handy when involving several screens, particularly if you have difficulty controlling the speed of the mouse when filling down. You will not be able to make full use of the fill handle if you are not also familiar with the use of Absolute and Relative cell references, you will find articles in HELP as topics “About cell and range references”, which points to better topic “The difference between relative and absolute references”, and while the help pages are very good they can't be directly linked to regardless of your version, which makes Chip Pearson's article the more accessible reference “Relative And Absolute Addressing” (Direct links to Excel Help on your machine is clueless as direct links would depend on the Excel version, so you will have to use the inadequate Help search facility).
The absolute reference refers to absolute with regards to fill handle, copy and paste, drag and drop, and other fill operations, it is not absolute with regard to inserting and deleting rows / columns. For something to never change you would have to use something that places the address within double quotes.
Filldown as a Macro solution (#filld)
The following macro will simulate fill down (Ctrl+D), as long as there is data to the left. The shortcut would do use right if it can't use the left.Sub filld() 'Simulate Ctrl+D (fill down), D.McRitchie 2005-06-14 programming ' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld If IsEmpty(ActiveCell) Then Exit Sub Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown End SubThe following will fill down as far down as the column to the left has content, as opposed to contiguous content. It will continue to fill down even if there are gaps in data to left but stops when there in no more data in the column to left.Sub filld_to_last_at_left() 'Fill down to lastrow based on cell to left, D.McRitchie 2005-06-14 programming ' http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld If IsEmpty(ActiveCell) Then Exit Sub Range(ActiveCell, Cells(Rows.Count, _ ActiveCell.Column - 1).End(xlUp).Offset(0, 1)).FillDown End Sub
| abc | 1 | =ROW(1:1) | A1 | =A1 | =E1+D2 | =OFFSET(E2,-1,0)+D2 | row 1 col 1 |
| abc | 2 | =ROW(2:2) | A2 | =A2 | =E2+D3 | =OFFSET(E3,-1,0)+D3 | row 1 col 2 |
| abc | 3 | =ROW(3:3) | A3 | =A3 | =E3+D4 | =OFFSET(E4,-1,0)+D4 | row 1 col 3 |
| abc | 4 | =ROW(4:4) | A4 | =A4 | =E4+D5 | =OFFSET(E5,-1,0)+D5 | row 1 col 4 |
| abc | 5 | =ROW(5:5) | A5 | =A5 | =E5+D6 | =OFFSET(E6,-1,0)+D6 | row 1 col 5 |
| abc | 6 | =ROW(6:6) | A6 | =A6 | =E6+D7 | =OFFSET(E7,-1,0)+D7 | row 1 col 6 |
| abc | 7 | =ROW(7:7) | A7 | =A7 | =E7+D8 | =OFFSET(E8,-1,0)+D8 | row 1 col 7 |
| abc | 8 | =ROW(8:8) | A8 | =A8 | =E8+D9 | =OFFSET(E9,-1,0)+D9 | row 1 col 8 |
The items in bold were replicated (propagated) down using the fill-handle.
In the US our dates are ordered
as mm/dd/yyyy, which you will see as the days being incremented
when a single cell was selected.
| formula | formula | formula | formula | date | date | series |
| =A1+1 | =A1+1 | =1+A1 | =ROW(1:1)+A1 | 01/01/2000 | 01/01/2000 | 2 |
| =A2+2 | =A2+1 | =1+A2 | =ROW(2:2)+A2 | 01/02/2000 | 02/01/2000 | 4 |
| =A3+1 | =A3+1 | =1+A3 | =ROW(3:3)+A3 | 01/03/2000 | 03/01/2000 | 6 |
| =A4+2 | =A4+1 | =1+A4 | =ROW(4:4)+A4 | 01/04/2000 | 04/01/2000 | 8 |
| =A5+1 | =A5+1 | =1+A5 | =ROW(5:5)+A5 | 01/05/2000 | 05/01/2000 | 10 |
| =A6+2 | =A6+1 | =1+A6 | =ROW(6:6)+A6 | 01/06/2000 | 06/01/2000 | 12 |
Use of fill-handle with superscripts involved
|
The table at the left involves superscripts.
It is interesting to note that there is a difference
in using the fill-handle to copy one cell down a single
column, and copying a row downward also with the fill-handle.
In other word when a row is replicated it is treated same as if CTRL
were held down at same time.
Incidental note, if you Copy and Paste cells with different font formatting in a cell be sure to copy from the cell and not from the formula line. Color, bold, italic, subscripts, and superscripts are font attributes and can be applied to individual characters in a cell. to copy from the cell not from the formula line. | ||||||||||||||||||||||||||||||||||||||||||
NumLock on: Alt+nnnn where nnnn is 0186 or 0185 or 0178 or 0179
These are separate characters so any use of the fill-handle will not change them. See symbols.
When using autoreplace and it changes something you don't want it to, you can immediately hit CTRL+z which is faster for most people than using the UNDO button or edit menu.
Tools -- Autoreplace change: m%%2 to m2 as in meters squared.
| A | B | C | D | E | F | G | H (as used in Column D) | |
| 1 | apples | apples | apples | =IF(COUNTIF(A:A,B1)=0,"",B1) | ||||
| 2 | beef | salt | salt | beef | salt | =IF(COUNTIF(A:A,B2)=0,"",B2) | ||
| 3 | beets | tea | tea | beets | tea | =IF(COUNTIF(A:A,B3)=0,"",B3) | ||
| 4 | butter | salt | salt | butter | salt | =IF(COUNTIF(A:A,B4)=0,"",B4) | ||
| 5 | corn | corn | corn | =IF(COUNTIF(A:A,B5)=0,"",B5) | ||||
| 6 | milk | mustard | mustard | =IF(COUNTIF(A:A,B6)=0,"",B6) | ||||
| 7 | oats | milk | milk | oats | =IF(COUNTIF(A:A,B7)=0,"",B7) | |||
| 8 | milk | sesame | sesame | =IF(COUNTIF(A:A,B8)=0,"",B8) | ||||
| 9 | pepper | fish | fish | pepper | =IF(COUNTIF(A:A,B9)=0,"",B9) | |||
| 10 | popcorn | salt | salt | popcorn | salt | =IF(COUNTIF(A:A,B10)=0,"",B10) | ||
| 11 | salt | rice | rice | =IF(COUNTIF(A:A,B11)=0,"",B11) | ||||
| 12 | tea | tea | tea | tea | =IF(COUNTIF(A:A,B12)=0,"",B12) | |||
| 13 | vinegar | vinegar | vinegar | =IF(COUNTIF(A:A,B13)=0,"",B13) |
| cell | =GETFORMULA(cell) | Description |
| D1 | =IF(COUNTIF(A:A,B1)=0,"",B1) | Exists in both Col A and Col B |
| E1 | =IF(COUNTIF(A:A,B1)=0,B1,"") | Exists only in Column B |
| F1 | =IF(COUNTIF(B:B,A1)=0,A1,"") | Exists only in Column A |
| G1 | =IF(COUNTIF(B:B,B1)>1,B1,"") | Duplicates within Col B |
See Chip Pearson's Duplicate And Unique Items In Lists for information on identifying duplicate and unique entries in a single column.
A B C D E F G H I J K 1 Smith 5 4 5 3 17 Smith1 37 =A1&"1" =G2 2 6 5 5 4 20 37 Smith2 37 =A1&"2" =G2 3 Jones 4 3 3 3 13 Jones1 33 =A3&"1" =G4 4 5 5 5 5 20 33 Jones2 33 =A3&"2" =G4 5 white 5 4 4 4 17 white1 30 =A5&"1" =G6 6 4 3 3 3 13 30 white2 30 =A5&"2" =G6 7 Pete 5 5 5 5 20 Pete1 38 =A7&"1" =G8 8 6 5 4 3 18 38 Pete2 38 =A7&"2" =G8 Data is contained in two rows per name (in A), only the second row has the grand total (in G), data is to be sorted by Grand Totals but keep the two rows together. Worksheet solution posted by "arno" 2004-07-06 [altG] in programming.
Sort on Column I descending, then on column H.
Excel sorts retain the order, so you actually only need the extra column that carries the Grand Total (G) for sorting. Carrying both extra columns provides the ability to sort on names instead (don't actually want the 1&2 suffix).
Based on a posting by George Simms on 2000-01-30 [Example] introducing a slick technique that simplifies replication using ROW(1:1). Having a distinct advantage over
ROW(A1), for instance.
=LARGE($A$1:$A$10,ROW(1:1))
The following will work note result: ='Team 1'!$B$2
=INDIRECT("'Team " & ROW(1:1) & "'!$B$2")
=INDIRECT("'Team " & ROW(2:2) & "'!$B$2")
=INDIRECT("'Team " & ROW(3:3) & "'!$B$2")
will generate formulas that act as if coded as follows:
='Team 1'!$B$2
='Team 2'!$B$2
='Team 3'!$B$2
A variation would be where the sheetname is in Column A
Team-AA =indirect("'" & A2 & "'!$B$2") i.e. 'Team-AA'!$B$2
Tiger-Team =indirect("'" & A3 & "'!$B$2") i.e. 'Tiger-Team'!$B$2
Wood Knots =indirect("'" & A4 & "'!$B$2") i.e. 'Wood Knots'!$B$2
All detail sheets have same format and we only want information from Column B of each detail sheet.
A B C D 1 Ages 1970 1971 each year shown is the name of a detail sheet) 2 5-12 35 38 35 is from cell b2 on sheet 1970 3 13-17 40 37 40 is from cell b3 on sheet 1970 Formulas for this example, and note that a single quote is placed in front of ages to prevent from being interpreted as a date.
A B C 1 Ages 1970 1971 2 '05-12 =INDIRECT("'" & B$1 & "'!B" & ROW()) =INDIRECT("'" & C$1 & "'!B" & ROW()) 3 '13-17 =INDIRECT("'" & B$1 & "'!B" & ROW()) =INDIRECT("'" & C$1 & "'!B" & ROW()) This is what the effective formulas would look like
A B C D 1 Ages 1970 (revised) 1971 1972 2 '05-12 ='1970 (revised)'=!$B2 ='1971'!$B1 ='1972'!$B2 3 '13-17 ='1970 (revised)'=!$B3 ='1971'!$B2 ='1972'!$B3 If the detail sheets 1970 and 1971 do not have descriptions at the top then adjust the row.
A B C 1 Ages 1970 1971 2 '05-12 =INDIRECT("'" & B$1 & "!'B" & ROW()-1) =INDIRECT("'" & C$1 & "!'B" & ROW()-1) 3 '13-17 =INDIRECT("'" & B$1 & "!'B" & ROW()-1) =INDIRECT("'" & C$1 & "!'B" & ROW()-1)
| =INDIRECT("'Sheet 1'!$A$"&ROW(1:1)) | =INDIRECT("'Sheet 1'!$B$" &ROW(1:1)) | =INDIRECT("'Sheet 1'!$C$" &ROW(1:1)) |
| =INDIRECT("'Sheet 1'!$A$"&ROW(2:2)) | =INDIRECT("'Sheet 1'!$B$" &ROW(2:2)) | =INDIRECT("'Sheet 1'!$C$" &ROW(2:2)) |
| =INDIRECT("'Sheet 1'!$A$"&ROW(3:3)) | =INDIRECT("'Sheet 1'!$B$" &ROW(3:3)) | =INDIRECT("'Sheet 1'!$C$" &ROW(3:3)) |
Another Example showing the sheet being changed but with the source cell being the same on each sheet was included above in a Team Summary page.
Similar code for showing top LARGE/SMALL were shown on my Formula page.
One way to tell if the cells are numeric constants without using functions is Select ALL --> Edit --> GoTo Special --> constants & numbers Using functions if you have a mixture of constants and formulas, run this down another column with fill-handle . For more information see my Formula page.=IF(ISNUMBER(A1),"N",IF(ISTEXT(A1),"Text","O"))
Other things found on my Formula page include formulas to show the formula used in another cell, the format used in another cell, the hyperlink used in another cell (URL, hyperlink).
Using a formula like =F2+E3 for a running total will work, but it is troublesome if you want to insert or delete lines, in which case the formula =OFFSET(F3,-1,0)+E3 is a lot less problematic. Read more about Inserting Rows.
The following do not involve the Fill Handle, but do fill downward
Some other methods that can replicate values into blank cells in a selectionThis came to my attention from a posting by Eddie Griffiths though I'd seen it earlier in John Walkenbach's article in How to Duplicate Values in an Excel List , and you can find a more graphical presentation on Debra Dalgleish's page, Excel -- Data Entry -- Fill Blank Cells
Programming solution: Equivalent to the above, but bolding the original text descriptions
- Select column A (or a more specific range -- A1:A40)
- CTRL+G (Edit --> GoTo), Special -- note: Special cells is restricted to the used range
- select Blanks
- Enter formula =A1 into cell A2 and then
- CTRL+Enter (Fill the selected cell range, blanks, with the current entry)
- Select column A (or a more specific range -- A1:A40)
- Copy using Ctrl+C
- Edit, Paste Special, Values
Also see Fill in the Empty Cells (fillempt.htm)
Sub Fill_Empty() '--David McRitchie, 2003-07-24, '--Macro version of -- Excel -- Data Entry -- Fill Blank Cells 'http://www.contextures.com/xlDataEntry02.html 'http://www.pcworld.com/shared/printable_articles/0,1440,9346,00.html Dim oRng As Range Set oRng = Selection Selection.Font.Bold = True Selection.SpecialCells(xlCellTypeBlanks).Select Selection.Font.Bold = False Selection.FormulaR1C1 = "=R[-1]C" oRng.Copy oRng.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub
A 1 Alex 2 3 4 Barb 5 6 Cleo 7 8
A 1 Alex 2 Alex 3 Alex 4 Barb 5 Barb 6 Cleo 7 Cleo 8 Cleo
A non-programming method:If you record a macro you would get something along the lines of:
- place a zero in an empty cell and copy it (Ctrl+c)
- Select a range that includes the empty cells that are to receive zeros.
- Ctrl+G (edit, goto), Special, blank cells
- Edit, paste special, values
Sub Macro1() Range("H3").Select Selection.Copy Range("F1:F22").Select Selection.SpecialCells(xlCellTypeBlanks).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False End Sub
The correct way is a lot smaller (Tim Zych, programming, 2001-09-21)On Error Resume Next ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = 0You can use Selection. in the above or a Range. instead of Activesheet.Used.Range.
(#mouse)Techniques for moving rows or columns; and inserting blank rows or columns all using the mouse cursor in combination with the CTRL, SHIFT, or ALT keys. (drag and drop is an option affects this and the fill-handle) -- Mouse OperationsYou will NOT find the following information in HELP. If you don't believe me try looking for any of the following using any of these key words: mouse, mouse operations, drag, dragging, grab, grabbing, cell border -- you will get hits but none of them point to the following information. If you don't know you have something then you don't really have it.
in Excel Tools->Options->General->"zoom on roll with intellimouse
keep this off or you won't be able to use mouse for
a vertical scrolling, you can still use Ctrl+wheel to zoom in/out.
My settings basically: Device: IntelliMouse Optical,
Left Button: Click (default), Right Button: Right-click (default),
Wheel Button: Enter, Left Side Button: Back (default),
Right Side Button: Forward (default).
Wheel tab: Scroll one "screen" at a time,
[Advanced]: Only turn off IntelliPoint support for: AutoCAD, Fractal ...,
Macromedia ..., MS PhotoDraw.
on the wheel tab, advanced
remove any disable exceptions for IE and for Visual Basic
I guess they thought they could do it better themselves, but I'm
pretty sure that was where the problem was with not being able to
to use the wheel to scroll within the VBE.
if you still can't get scroll to work in VB then check out Shahin Gasanov's VBScroll at
http://www.gasanov.net/VBScroll.htm [ref]
but make sure it is not in advance options on wheel button,
or the buttons(tab), Settings (button)
All my mouse settings have been put in mouse.txt
http://www.mvps.org/dmcritchie/excel/mouse.txt and includes
Additional Details for [IrfanView] [Mozilla] [Excel] [Power Point] [Quicken 4th: PageUp, 5th: PageDn]
Click to see default system mouse Pointers, and to see descriptions for pointer shapesSee the VBA help for
Application.Cursor = xlWait 'hourglass -- see VBA help "MousePointer Property"
Application.Cursor = xlDefault 'restore default square cross
'--other options: xlDefault, xlBeam, xlNorthwestArrow, xlWaitAlso see VBA mouse code (below)
and other non-intuitive menu usage.
(Will probably be moved to toolbars.htm with references in shortx2k.htm and fillhand.htm)
- copying a picture (also see xl2gif.htm)
- Shift+Edit / Copy Picture
- Shift+Edit / Paste Picture
- Shift+Edit / Paste Picture Link
- closing all files
- Shift+File / Close All
- always create a backup
- File / SaveAs / Options / Always create a backup
which became, in 2000
File / SaveAs / Tools / General Options / Always create a backup
(and similarly read-only recommended)- switch between read-only and read-write
- get Toggle Read-Only toolbar button from File category or write own macro to do it
- select whole of a table
- Edit / Goto / Special / Current Region
- or Ctrl+*
- or customise toolbar with Select Current Region button from Edit category
(and similarly other Edit / Goto / Special options)
Hold the shift key down when scrolling to speed up scrolling. This will make the scroller smaller. The size of the scroller is normally despendent on the the number of used rows. If you don't have much data and have a small scroller, it is an indication of of your lastcell (Ctrl+End) incorrectly set. To fix last cell problems see particularly Reset_lastcell and MakeLastCell macros. More information on Scrollbars.
More on Shortcut keys, Fill-Handle is not involved.
Fill down, fills down constna CTRL+D Fill to the right CTRL+R wipes out fill to right Ctrl+Shift+R Fill the selected cell range with the current entry CTRL+ENTER
- Place a constant or a formula in cell; =ROW()
- Place a range in the name box; A1:A200
- Fill down from the active cell, and only from the active cell; Ctrl+R
The Spacebar is used for quick entry from within locations where you can't type. In Internet Explorer and in Outlook Express, you can use it to Page Down. In Excel you can use it to Select a dialog button instead of clicking on it (use TAB key for next choice). You can Maximize a Window including IE, OE, and Excel, and Excel Help, with Alt+SpaceBar+X. or Restore a Window with Alt+SpaceBar+R. If you lose the Titlebar under the frame you can Move the Window (pull the TitleBar Down) with Alt+Spacebar the Move.Some Excel shortcuts using the spacebar (all can be done with the left hand):
Perform the action assigned to the active button (the button with the dotted outline), or select or clear the active check box SPACEBAR Note To enlarge the Help window to fill the screen, press ALT+SPACEBAR and then press X. To restore the window to its previous size and location, press ALT+SPACEBAR and then press R. ALT+SPACEBAR+X
ALT+SPACEBAR+RWith an object selected, select all objects on a sheet CTRL+SHIFT+SPACEBAR Select the entire column CTRL+SPACEBAR Select the entire row SHIFT+SPACEBAR Select or clear a check box in the list SPACEBAR Show the program icon menu (on the program title bar) ALT+SPACEBAR A more complete list of Excel shortcuts is available.
Using the fill handle. Select a cell, you will see a little black square at the lower left corner. Grab the fill handle using the left mouse button drag down as far as you want, or if there are things to the left -- double-click on the fill handle and it will be replicated down automatically.Auto Filter.
Listed will be aol.com
- Select all cells (Ctrl+a)
- Data --> Filter --> AutoFilter
- select arrow in cell D1
- make a choice from the alphabetic list i.e. aol.com
To see everything again choose “All” on the filter arrow.
To turn off autofilter: Data --> Filter --> click to uncheck AutoFilter
A B C D E 1 Name Location Userid ISP Formula used in Column D
Using GetFormula2 A2 B2 A2@aol.com aol.com =MID(C2,FIND("@",C2)+1,99) 3 Wagner B3 A3@Laramie.edu Laramie.edu =MID(C3,FIND("@",C3)+1,99) 4 A4 B4 A4@aol.com aol.com =MID(C4,FIND("@",C4)+1,99) 5 A5 B5 A5@yahoo.com yahoo.com =MID(C5,FIND("@",C5)+1,99) 6 Robert B6 Robert@deja.com deja.com =MID(C6,FIND("@",C6)+1,99) 7 Larry B7 larry@fakeisp.com fakeisp.com =MID(C7,FIND("@",C7)+1,99)
By grouping your worksheets, you can make the same change manually to several sheets at once. Select a sheet and with the Ctrl key, select additional sheets. Another way of grouping sheets is Right-click then “Select All Sheets”.When Grouping of sheets is in effect you will see [Group] on the title bar after the filename. (don't confuse with multiple views :1, :2, :3).
Any change you make to the active worksheet will be made to all of the grouped worksheets whether they be constants or formulas.
WARNING: Don't forget to ungroup your sheets when finished or you may cause severe damage to your workbook.
To ungroup sheets: Select a sheet outside the group, or right-click sheet tab and choose “ungroup sheets”.
With Grouped sheets you can change at least some of page setup options. Macros will not process grouped sheets unless specifically coded to do so. Example of how a macro can be modified is the INSRTROW macro on my Insert rows maintaining formulas (insrtrow.htm) page.
As an alternative to grouping sheets you might want to linke the values ='Sheet4'!A4
Some specific reasons to Group sheets (#grouped -- topic actually starts above)
Repeating warning: Ungroup your worksheets immediately when finished, failure to ungroup will destroy your workbook if grouping remains in place.
- Deleting several sheets, is the most common reason that I group sheets.
- Same wording or formulas. Placing same constants or formulas (anything you type) into the same location on all of the grouped sheets.
- Selected cell. Selecting a cell such as A1 so that all of the grouped sheets have the same cell selected.
- Page Setup. You can manually change file, setup things such as headers and footers. I think you can change everything *except* the grayed out select a print area, rows to repeat at top, columns to repeat at left.
- Change things like formatting, gridlines, borders, style, for grouped sheets.
- Convert formulas to values with Select All, Copy, Paste Special Values.
fill handle is missing: If the fill handle is missing go into tools, options, Edit, and check Allow cell drag and drop.
formulas don't change (same values) when using fill handle and a quick check with F2 then Enter will update formula – Turn calculation back on
Tools, calculate (tab), calculation: automatic
Keywords: problems grabbing text with cursor selection release(2004-04-11) Trying to grab text such as several cells in Excel, text in text, or HTML results in unpredictable selections. Selection highlighting may disappear when releasing mouse or change the selection to more, fewer, or different characters. Had a problem a few months ago with LockerGnome newsletters (not the web site) unitil they changed them back.
Current workarounds that may work for some usages. Select text by multiple clicks to get perhaps the entire word, sentence, or paragraph. If the purpose is to delete hit the Del key before releasing mouse button to lock the selection, then Delete. Triple clicking in HTML gets a paragraph as does Ctrl+click (in HTML). Place cursor down and use arrow key and delete keys to delete text since it's hard to select text.
This has been bugging me last night and most of today, defragged and rebooted last night not no change, but as soon as finished writing this up the problem disappeared.
Don't think I changed any Accessibility Options. Had “Word Web 2.2” running, stopped the program and seems to be a slight improvement but not much, but as soon as I finished writing this up the problem disappeared. Brought up Word Web again and had the problem again momentarily but okay again. Found that there is a 3.1 free version http://wordweb.info/free/ so will try that later. Am still experiencing some problems even though Word Web was just uninstalled. Brought up 3.1 more problems, took it down, still has problems.
Here's a thread to check on the subject:
http://groups.google.com/groups?threadm=%235ZCGvAMBHA.1400%40tkmsftngp03I had closed the mouse on the system tray and the wheel worked in Excel but not in the VBE. Can be fixed by going to control settings mouse, ok. Then mouse will be back in systray. I no longer show the mouse in the systray, but it has to be active and I can see mine in the running tasks.
To test now. terminated point32.exe from runnings tasks with task manager (Ctrl+Alt+Del). Mouse wheel works in Excel but not the code window of the VBE. Settings, Control Panel, Mouse, didn't do anything but press the [OK] button. Mouse wheel works again in the VBE, and point32.exe shows up in the Task Manager. Mouse does not show up on the System Tray and I don't want to see it there even though it has to be running.
I see point32.exe in system Information, Software Environment, Startup Programs but that is not where ones adds or removes such things.
One more point, my Wheel click is set Enter instead of AutoScroll. Dislike AutoScroll and think it a possible cause of ghosting problems, another possibility of ghosting problems is crippled use of CELL Worksheet Function described on pathname.htm page when used without the cell reference, and the other often mentioned not having latest mouse drivers..
Another item you will not find in Excel Help.When you start Tabbing across a row and then hit ENTER (default down), then the cursor will return to the cell below the cell you started tabbing from. [Ragdyer in misc 2004-06-07]. ]
[don't panic, It's just a picture of an hour glass]See Mouse Pointers (above)Application.Cursor = xlWait 'turn on hourglass pointer <Your code here > Application.Cursor = xlDefault 'return normal pointerFor more information look up the following in VBA Help: “Mousepointer property”, and “cursor property”.
How to install/use a macro can be found on my formula page.
Please send your comments concerning this web page to: David McRitchie send email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved