='sheet three'!D3 =sheet36!D3 ='[otherworkbook.xls]some other sheet'!$B$7
This page is a conglomeration of aspects of cell related coding snipets. Currently this page remains NOINDEX
Cells, last used or next free in a column (#next)last cell in column A within the same region as cell A1:
last cell even with empty cells in the column you could use
Range("A65535").End(xlUp).select '-- Example only
Range("A" & Rows.Count).End(xlUp).Select
Cells(Rows.Count,1).End(xlUp).Select '-- preferred usage
Use of xldown xlup xltoRight xltoLeft
The use of 65536 to identify the last row is for illustration purposes only, do not code constants into a program as the last row and hopefully the last column can and will change between versions of Excel. Instead use Rows.Count and Columns.Count -- For examples of these in macros with toolbar icons see Toolbars page.Last used row in column A: => 8 Worksheets("Sheet1").Range("A65536").End(xlUp).Row Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row Next available empty row in column A: => 9 Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1 Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1 xlDown looks for the first empty cell it finds and returns the last used row before that first empty cell, so: 'Last used row before the first empty cell: => 3 Worksheets("Sheet1").Range("A1").End(xlDown).Row 'First empty cell in column A => 4 Worksheets("Sheet1").Range("A1").End(xlDown).Row + 1 If you want to find the last possible row all the way down column A, use xlUp.
As improbable as it seems you can do something similar with a Worksheet Function. The following array formula [ctrl+Shift+enter] will provide the row number for the last cell in Column A. Dave Perterson 2001-12-23 in Worksheet.Functions
Last Column in a RowLast cell even with empty cells in the row you could use
Range("A" & Columns.Count).End(xlLeft).Select
Columns 16 through end (#select)Identification of the last cell address -- varies with different versions MsgBox Cells(Rows.Count, Columns.Count).Address '-- i.e. IV65536 or TT2000000 in Excel 12.
Range(Cells(1, 16), Cells(1, Columns.Count)).EntireColumn.Select
Dim rng as Range set rng = worksheets("Dest").Cells(rows.count,1).end(xlup)(2) worksheets("Sheet1").Range("D24").Copy Destination:=rng Worksheets("Sheet1").Range("F23").Copy Destination:=rng.offset(0,1)
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) MsgBox Selection.Address & Chr(10) _ & Selection.Item(1).Address & Chr(10) _ & Selection.Item(Selection.Count).Address End SubAll four corners:
Msgbox "TopLeft: " & Selection.Cells(1).Address _ & " TopRIght: " & _ Selection.Cells(1, Selection.Columns.Count).Address _ & Chr(10) & "BotLeft " & Selection.Cells(Selection.Rows.Count, 1).Address _ & " BotRight " & Selection.Cells(Selection.Cells.Count).Address
'Delete Columns Range("C:C,E:G,I:I,K:K,M:S,U:U,W:W,Y:Y").Delete xlToLeft 'Delete Rows Range("14:400").Delete
Some test coding leading up to the Isolate_selection macroGoTo skip_over Range("A1:M30").Select Application.Run "personal.xls!MarkCells" Range("a1:g10, j12:k12").Select 'GoTo skip_over MsgBox Selection.Areas.Count & " in " & Selection.Address MsgBox ActiveSheet.UsedRange.Address & " -- Used Range address" MsgBox Selection.Address & " -- " & Selection.Areas(1).Address MsgBox Selection.Count & " -- " & Selection.Areas(1).Count MsgBox Selection.Areas(1)(Selection.Areas(1).Count).Address MsgBox ActiveSheet.UsedRange.Address MsgBox Cells.SpecialCells(xlLastCell).Column MsgBox Cells(Rows.Count, Columns.Count).Address & " -- lastcell address" skip_over: Dim shouldsee As String Dim rng1_address As String, rng2_address As StringThe MarkCells macro can be found at join.htm#markcells and the code at code/join.txt
Isolate_Seletion -- delete everythin not in First area (#isolate_seletion)Sub Isolate_selection() 'Delete all cells not within the first or only selection area 'David McRitchie, excel.misc, cells.htm 2005-11-17 Dim shouldsee As String Dim rng1_address As String, rng2_address As String shouldsee = Selection.Areas(1).Address(0, 0) Range(shouldsee).Select '-- identify top left cell and bottom right cell of area to be kept rng1_address = Range(Selection(1)(1)).Address rng2_address = Range(Selection.Areas(1)(Selection.Areas(1).Count).Address) On Error Resume Next 'important if no rows and/or columns before seletion '-- delete rows below Selection.areas(1) ... Range(Range(rng2_address).row + 1 & ":" & Cells.Rows.Count).Delete '-- delete columns to right of Selection.areas(1) ... ' Range(Range(rng2_address).Offset(0, 1) & ":IV65536").EntireColumn.Delete Range(Range(rng2_address).Offset(0, 1).Address & ":" & _ Cells(Rows.Count, Columns.Count).Address).EntireColumn.Delete '-- delete columns to left of selection.areas(1) ... Range("a1:" & Range(rng1_address).Offset(0, -1)).EntireColumn.Delete '-- delete rows before selection.areas(1) ... Range("A1:" & Range(rng1_address).Offset(-1, 0)).EntireRow.Delete MsgBox "you should see your original " & should see ' ActiveWorkbook.Save '-- fix last cell End Sub
Deletion Related (#deletion-related)
- Delete rows and columns past the active cell
- Reduce the size of the workbook by eliminating the rows and columns past the actual data, where the last cell indication Ctrl+End is incorrect.
"Why do my scrollbars go to row 500 -- my data ends in cell E50?"
Delete the rows and columns outside of a single selection area.
See example within this topic.
Leave the single selection area intact but clear the contents but not delete cells outside a selection(s).
would be similar to the macro here but would clearcontents instead of delete rows and columns.
- Identification of the current region [Ctrl+SHIFT+*], could be used as a selection. but you'd probably not have complete control of what you are working with.
This page was created on June 10, 2000.
Please send your comments concerning this web page to: David McRitchie send%20email comments
Copyright © 1997 - 2004, F. David McRitchie, All Rights Reserved