Approach to find last row or column using VB script in Excel?

We are sharing these techniques so that we can work with dynamic set of data in an Excel.

Finding last row of excel using code

Do follow us for more learning’s Fb,G+,Twitter.

Technique 1:- 
Using UsedRange property:- 
This approach is frequently implemented and one of our favorite. But sometime we have seen that incase some formatting is present in the sheet, it points till the last range of the excel (like 1,048,576 rows in excel -2013) itself rather than the last occupied row of the sheet. 

Technique 2:-

  • Using “CTRL+SHIFT+END” to find the last row 
  • Using “CTRL+SHIFT+Down Arrow” to find the last row 
  • Using “CTRL+SHIFT+left/Right arrow” to find the last column
'Function Name:- fn_CalRange
'Function Description:- Function to Calculate the last row & last column
'Input Parameters:- N/A
'Output Parameters:- N/A
Sub fn_CalRange()

Dim iLastRow
Set objExcel = CreateObject("Excel.Application")
'Making Excel visible to user
objExcel.Visible = True
Set oCountWB = objExcel.Workbooks.Open("D:Test.xlsx")
Set oCountWS = oCountWB.Worksheets("Sheet1")

'Counting the Last row using 'Range' Property
iLastRow = oCountWS.UsedRange.Rows.Count
'Counting the Last Column using 'Range' Property
iLastCol = oCountWS.UsedRange.Columns.Count
MsgBox "Using Range Property Last Row: " & iLastRow & " Last Column:" & iLastCol

'-4162 meaning xlUp
'Using CTRL+SHIFT+END to find the last row
iLastRow = oCountWS.Cells(oCountWS.Rows.Count, "A").End("-4162").Row
MsgBox "Using CTRL+SHIFT+END, Last Row:" & iLastRow

'*****************CTRL+SHIFT+Left Arrow*************
'Using CTRL+SHIFT+left arrow to find the last Column
'-4159 for xlToLeft
iLastCol = oCountWS.Cells(1, oCountWS.Columns.Count).End("-4159").Column
MsgBox "Using CTRL+SHIFT+Left Arrow, Last Column:" & iLastCol

'*****************CTRL+SHIFT+Down/Right Arrow************
iLastRow = oCountWS.Range("A1").CurrentRegion.Rows.Count
iLastCol = oCountWS.Range("A1").CurrentRegion.Columns.Count
MsgBox "Using CTRL+SHIFT+Down/Right Arrow,Last Row:" & iLastRow & " Last Column:" & iLastCol

End Sub

More Excel Examples

SQL Interview Questions (Set-1)

This is our first set of SQL interview Questions.

Prepare well and crack your dream job. It is said,“Fall seven times, stand up eight.”(Japanese proverb), so work hard to achieve heights.

Work hard to crack SQL interview
Work Hard

Follow us on Facebook for more updates and learning!!!

  1. What are various types of SQL statements?
  2. What is the difference between Truncate, Drop and Delete?
  3. Difference between Local and Global Temp tables?
  4. What are indexes and types of indexes?
  5. How to copy only structure of Table A to Table B?
  6. Meaning of BCP in SQL?


a. Data definition language
  1. Create
  2. Alter
  3. Drop
  4. Truncate

b. Data Manipulation language 
  1. Select
  2. Insert
  3. Delete
  4. Update

c. Data Control Language
  1. Grant 
  2. Revoke

d. Transaction control statements
  1. Commit
  2. Rollback
  3. Save point 

2. Delete, is used to remove rows from a table, we can use where clause to limit the rows to be deleted. After delete we need to perform Commit or Rollback operation to make the changes permanent, thus we can say we have chance to undo the delete operation .It is a DML command. Delete will cause delete triggers on the table to be fired.

Truncate, it is used to remove all rows from a table. The operation cannot be rolled back and no triggers are fired. It is a DDL command and faster as It does not use the concept of undo operation thus even logs are not maintained for the deleted data.

Drop, is more dangerous as it removes table from the database unlike above commands which were only removing rows from the table. Like truncate it cannot be rolled back and no triggers are fired in this case, further this also a DDL command.

3. Temporary tables (temdb) are automatically deleted when they are not in use. 
  1. Local, It is prefixed with  single ‘#’ when while creation. Like create table #Study(ID int not null). Further they are available only to the connection that creates it and are deleted when the connection is closed. We can create a same local temp table with the same name but in a different connection.
  2. Global, It is prefixed by ‘##’ values, like create table ##Study (ID int not null).They are visible to all the connections of SQL server and are destroyed only when the last connection referencing the table is closed.

4Index: – It is an structure associated with a table or view which speed up the query retrieval time. An index contains keys which are combination of one or more columns of table or view. These keys are stored in B-Tree structure for efficient search of data from a table.

Clustered Index:- Example like phone directory, where each entry in the directory represents one row of the table. A table can have only one clustered index.
Non Clustered Index: – Example , index at the back of the book, where indexed values contains pointers to the actual rows(page number in a book).

Syntax:-Create index (index name) ON table_Name (Column name)

5. Select * into TableB from TableA where 0=1

6. BCP (Bulk copy Program) is a command line utility to bulk copy data between an SQL server and a data file (both in or out).We can use table or query to export/import data. Example we need to move data from database A to file B , so we would write bcp database A .TABLE A out file B.

How to randomize rows of data in a Excel?

Problem:- How to randomize the given set of data in an Excel?

Solution: – We would try to shuffle the rows of data in a given excel. We would try it manually first followed by code written in Vb-script.

Using RAND() FUNCTION to shuffle excel data
Manual Approach:- 
Step1:- Add column(say Random) in the starting of the excel

Step2:- Apply formula to the first cell (=Rand ()) and drag it across all the cells of the same column in the sheet.

Step3:- Apply on the Filter on the first row of the excel (Data – > Filter)

Step4:- Now sort the Column one data in ascending order .All set we have our randomize data in the end.

Automation Approach:- 
We would use Vbscript code to automate the randomization of excel data. We would replicate the above manual steps by using coding.Please refer our tutorial on Excel automation to understand the below code.

Please do the following changes to run the below code:-

  • sFileName = Change it to Your file that need to be randomized
  • Change the sheetname in the code line   Set oReadSheet = oReadWB.Worksheets(“Sample”)

'Function Call to Randomize data in Excel

'Function Name:- fn_Randomize
'Function Description:- Function to shuffle Excel data
'Input Parameters:- N/A
'Output Parameters:- N/A
Sub fn_Randomize()
'FileName of the Excel
Dim sFileName
sFileName = "D:AutomateDataSheetRandomTest.xlsx"
'Creating the Excel Object
Set oExcel = CreateObject("Excel.Application")
'Making Excel Visible
oExcel.Visible = True
'Creating the Workbooks object
Set oReadWB = oExcel.Workbooks.Open(sFileName)
'Creating the Read sheet object
Set oReadSheet = oReadWB.Worksheets("Sample")
'Inserting 'Random' column as our first column in the sheet
oReadSheet.Range("A1").Value = "Random"
'Running the loop till for all the rows of excel which have data
For iRow = 2 To oReadSheet.UsedRange.Rows.Count
'Applying Random formula on the first column of the cell
oReadSheet.Cells(iRow, 1) = "=Rand()"
'Copying only the value and removing the formula from the cell
'Try commenting the below line of code and see the cell still contains "=Rand"
oReadSheet.Cells(iRow, 1).Value = oReadSheet.Cells(iRow, 1).Value
'Now sorting the Data
'Clearing any formuales on the Temp sheet
'Sorting on the basis of Colunm =1 in the asencding order
Call oReadSheet.Sort.SortFields.Add(oReadSheet.Columns(1), , xlAscending)
'Giving the range to be sorted(till the end of the sheet)
'Start cell is A2 as we have header on the Top
'End of sheet is taken as XFD1048576 as per Excel-2007
Call oReadSheet.Sort.SetRange(oReadSheet.Range("A2:XFD1048576"))
'Applying the sort
MsgBox "Data is Randomized..Cheers!!"
Set oExcel = Nothing
End Sub