How do you select the entire excel sheet with Range using VBA

Question

I found a similar solution to this question in c  How to Select all the cells in a worksheet in Excel Range object of c    What is the process to do this in VBA   I select data normally by using  ctrl shift over arrow  down arrow  to select an entire range of cells  When I run this in a macro it codes out A1 Q398247930  for example  I need it to just be   SetRange Range  A1 whenever I run out of rows and columns     I could easily do it myself without a macro  but I m trying to make the entire process a macro  and this is just a piece of it    Sub sort        sort Macro     Range  B2   Select     ActiveWorkbook Worksheets  Master   sort SortFields Clear     ActiveWorkbook Worksheets  Master   sort SortFields Add Key  Range  B2            SortOn  xlSortOnValues  Order  xlAscending  DataOption  xlSortNormal     With ActiveWorkbook Worksheets  Master   sort          SetRange Range  A1 whenever I run out of rows and columns            Header   xlNo          MatchCase   False          Orientation   xlTopToBottom          SortMethod   xlPinYin          Apply     End With End Sub   edit  There are other parts where I might want to use the same code but the range is say  C3 End of rows  amp  columns   Is there a way in VBA to get the location of the last cell in the document

User · Accepted Answer

I believe you want to find the current region of A1 and surrounding cells - not necessarily all cells on the sheet  If so - simply use    Range  A1   CurrentRegion

User · Answer

I would recommend recording a macro  like found in this post   Excel VBA macro to filter records   But if you are looking to find the end of your data and not the end of the workbook necessary  if there are not empty cells between the beginning and end of your data  I often use something like this   R   1 Do While Not IsEmpty Sheets  Sheet1   Cells R  1       R   R   1 Loop Range  A5 A   amp  R  Select  This will give you a specific selection   You are left with R   to the number of the row after your data ends  This could be used for the column as well  and then you could use something like Cells C   R  Select  if you made C the column representation

User · Answer

Maybe this might work   Sh Range  A1   Sh Range  A   amp  Rows Count  End xlUp

User · Answer

Refering to the very first question  I am looking into the same  The result I get  recording a macro  is  starting by selecting cell A76   Sub find last row       Range  A76   Select     Range Selection  Selection End xlDown   Select End Sub

User · Answer

You can simply use cells select to select all cells in the worksheet   You can get a valid address by saying Range Cells Address      If you want to find the last Used Range where you have made some formatting change or entered a value in you can call ActiveSheet UsedRange and select it from there   Hope that helps

User · Answer

Here is what I used  I know it could use some perfecting  but I think it will help others       STYLING    Dim sheet As Range    Find Number of rows used Dim Final As Variant     Final   Range  A1   End xlDown  Row    Find Last Column Dim lCol As Long     lCol   Cells 1  Columns Count  End xlToLeft  Column  Set sheet   ActiveWorkbook ActiveSheet Range  A   amp  Final  amp      Cells 1  lCol    With sheet      Interior ColorIndex   1 End With

User · Answer

you can use all cells as a object like this     Dim x as Range Set x   Worksheets  Sheet name   Cells   X is now a range object that contains the entire worksheet

User · Answer

Sub SelectAllCellsInSheet SheetName As String      lastCol   Sheets SheetName  Range  a1   End xlToRight  Column     Lastrow   Sheets SheetName  Cells 1  1  End xlDown  Row     Sheets SheetName  Range  A1   Sheets SheetName  Cells Lastrow  lastCol   Select End Sub   To use with ActiveSheet   Call SelectAllCellsInSheet ActiveSheet Name

User · Answer

I have found that the Worksheet   UsedRange  method is superior in many instances to solve this problem  I struggled with a truncation issue that is a normal behaviour of the   CurrentRegion  method  Using   Worksheets  Sheet1   Range  A1   CurrentRegion   does not yield the results I desired when the worksheet consists of one column with blanks in the rows  and the blanks are wanted    In this case  the   CurrentRegion  will truncate at the first record   I implemented a work around but recently found an even better one  see code below that allows copying the whole set to another sheet or to identify the actual address  or just rows and columns    Sub mytest GetAllUsedCells in Worksheet       Dim myRange      Set myRange   Worksheets  Sheet1   UsedRange      Alternative code   set myRange   activesheet UsedRange      use msgbox or debug print to show the address range and counts    MsgBox myRange Address          MsgBox myRange Columns Count    MsgBox myRange Rows Count     Copy the Range of data to another sheet    Note  contains all the cells with that are non-empty    myRange Copy  Worksheets  Sheet2   Range  A1        Note   transfers all cells starting at  A1  location               You can transfer to another area of the 2nd sheet            by using an alternate starting location like  C5    End Sub

User · Answer

you have a few options here    Using the UsedRange property find the last row and column used use a mimic of shift down and shift right   I personally use the Used Range and find last row and column method most of the time   Here s how you would do it using the UsedRange property   Sheets  Sheet Name   UsedRange Select   This statement will select all used ranges in the worksheet  note that sometimes this doesn t work very well when you delete columns and rows   The alternative is to find the very last cell used in the worksheet  Dim rngTemp As Range Set rngTemp   Cells Find      SearchOrder  xlByRows  SearchDirection  xlPrevious  If Not rngTemp Is Nothing Then     Range Cells 1  1   rngTemp  Select End If   What this code is doing    Find the last cell containing any value select cell 1 1  all the way to the last cell

User · Answer

Another way to select all cells within a range  as long as the data is contiguous  is to use Range  A1   Range  A1   End xlDown  End xlToRight   Select

[excel] How do you select the entire excel sheet with Range using VBA?

Examples related to excel

Examples related to vba

Examples related to excel-2010