All posts by AnalystCave

Tom. Excel / VBA / C# enthusiast and hobbist. Collecting and sharing my knowledge and experience with beginner/advanced analysts and VBA developers. My posts are written with one thing in mind: teaching analysts how to do things properly.
How to create an MS Query in Excel

How to create a Microsoft Query in Excel (Excel Query)

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

You can use Microsoft Query in Excel to retrieve data from an Excel Workbook as well as External Data Sources using SQL SELECT Statements. Excel Queries created this way can be refreshed and rerun making them a comfortable and efficient tool in Excel

A Microsoft Query (aka MS Query, aka Excel Query) is in fact an SQL SELECT Statement. Excel as well as Access use Windows ACE.OLEDB or JET.OLEDB providers to run queries. Its an incredible often untapped tool underestimated by many users!

You can extract data from:

  • Excel Files – you can extract data from External Excel files as well as run a SELECT query on your current Workbook
  • Access – you can extract data from Access Database files
  • MS SQL Server – you can extract data from Microsoft SQL Server Tables
  • CSV and Text – you can upload CSV or tabular Text files

How to Create a Microsoft Query

In this step by step tutorial I will show you how to create an Microsoft Query to extract data from either you current Workbook or an external Excel file.

I will extract data from an External Excel file called Data Source.xlsx situated in C:\.

The below process shows how you can create a query for your current or an external Excel Workbook. However, the process for creating a MS Query for Access, SQL and Text (CSV) files is very similar

Open the MS Query (from Other Sources) wizard

Create a Microsoft  Query (QueryTable)Go to the DATA Ribbon Tab and click From Other Sources. Select the last option – From Microsoft Query.

Select the Data Source

Create a Microsoft Query (QueryTable)Next we need to specify the Data Source for our Microsoft Query. Select Excel Files to proceed.

Select Excel Source File

querytableCreate a Microsoft Query (QueryTable)3Now we need to select the Excel file that will be the source for our Microsoft Query. In my example I will select my current Workbook, the same from which I am creating my MS Query.

Select Columns for your MS Query

Create a Microsoft Query (QueryTable)3The Wizard now asks you to select Columns for your MS Query. If you plan to modify the MS Query manually later simply click OK. Otherwise select your Columns.

Return Query or Edit Query

Create a Microsoft Query (QueryTable)3Now you have two options:

  1. Return Data to Microsoft Excel this will return your query results to Excel and complete the Wizard

  2. View data or edit query in Microsoft Query this will open the Microsoft Query window and allow you to modify you Microsoft Query

Optional: Edit Query

Create a Microsoft Query (QueryTable)3If you select the View data or edit query in Microsoft Query option you can now open the SQL Edit Query window by hitting the SQL button. When you are done hit the return button (the one with the open door).

Import Data

Create a Microsoft Query (QueryTable)3Lastly, when you are done click OK on the Import Data window to complete running the query.

MS Query Trick

The above process is long and cumbersome. How about doing it quicker?
create ms query
Just use my VBA Code Snippet:

Just create a New VBA Module and paste the code above. You can run it hitting the CTRL+SHIFT+S Keyboardshortcut or Add the Macro to your Quick Access Toolbar.

Power Query vs Microsoft Query

Lastly, I would like to tackle the question of Why use MS Queries when I have Power Query? Microsoft has done a good job of understanding that users need a tool to transform data, but often don’t have the knowledge to use the SQL Language to create SELECT queries. That is why they created the Power Query AddIn (as part of Power BI Suite).

MS Query Pros: Power Query is an awesome tool, however, it doesn’t entirely invalidate Microsoft Queries. What is more, sometimes using Microsoft Queries is quicker and more convenient and here is why:

  • Microsoft Queries are more efficient when you know SQL. While you can click your way through to Transform Data via Power Query someone who knows SQL will likely be much quicker in writing a suitable SELECT query
  • You can’t re-run Power Queries without the AddIn. While this obviously will be a less valid statement probably in a couple of years (in newer Excel versions), currently if you don’t have the AddIn you won’t be able to edit or re-run Queries created in Power Query

MS Query Cons: Microsoft Query falls short of the Power Query AddIn in some other aspects however:

  • Power Query has a more convenient user interface. While Power Queries are relatively easy to create, the MS Query Wizard is like a website from the 90’s
  • Power Query stacks operations on top of each other allowing more convenient changes. While an MS Query works or just doesn’t compile, the Power Query stacks each transform operation providing visibility into your Data Transformation task, and making it easier to add / remove operations

In short I encourage learning Power Query if you don’t feel comfortable around SQL. If you are advanced in SQL I think you will find using good ole Microsoft Queries more convenient. I would compare this to the Age-Old discussion between Command Line devs vs GUI devs

excel pictograph featured

Excel Pictograph – Charts with Pictures

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Today something much lighter than usual, more to do with presenting data than just crunching itHow to Create an Excel Pictograph? Or, in other simpler words, How to Create an Excel Chart with Images!

Images are Powerful Stuff! Our Brain processes Images 60 Thousand Times Faster than Text! For us Consultants, Marketers and Office Clerks this is important to understand, although often neglected. Hence the term Death by PowerPoint, known also as Death By Slideas in overwhelming your audience with an abundance of text and information squeezed into a single slide.

If you want to get Your Message across use Pictures instead. Today I will show you a simple, but effective Excel Trick you can use to make the most out of your Data Visualizations – to transform Dull Charts into Impressive Infographics.

How to Create an Excel Pictograph?

example excel pictographLet’s start with the Basics. An Excel Pictograph is a Chart that replaces standard Bars, Pies and other Graphic Elements with Pictures. Creating Excel Pictographs is incredibly easy, hence making it an incredibly Powerful Excel Tip / Trick.

Create an Excel Chart

To create an Excel Pictograph we need to have a Chart to work on first. If you are new to Excel Charts feel free to listen to Chandoo’s Excel Chart Tutorial here. I created a simple Chart below:

Simple Boring Excel Chart
Simple Boring Excel Chart

Find an Image Icon for the Chart

The Chart above is Boring. Isn’t it? Just Raw Data. It takes a moment to take it in. Now let’s find an interesting image to replace the Boring Barchart! Here are some great resources I use to find Image Icons:

  1. IconFinder a great database of Free and Premium Image Icons. I really appreciate the large amount of free images they have you don’t need to link back to

  2. Icon Archive a clipart image database

  3. Icon Finder yet another icon database

Be sure to find an Icon that will suitably visual the data you are trying to exemplify.

Paste the Image to your Excel Spreadsheet

past image to excelNow copy your image to your Excel Worksheet. To do that go to the INSERT Ribbon Excel tab. Select Pictures if you have the Image on your workstation, or Online Pictures if you need to download the Image from the Web. Follow the steps and hit Insert.

Copy & Paste the Image onto your Chart

This is the important part:
past image to excel chart

  1. Copy the Image by clicking on it and hitting CTRL+C

  2. Select the Data Visual select your Charts Bars, Pie slices or any other Data Visual Element which you want to replace with an Image

  3. Paste the Image by hitting CTRL+V

Configure the Image

The Chart is starting to look good. But often stretching the Image distorts it. We need to replace the Stretch with either the Stack or Stack and Scale with options:
excel pictograph configure image settings

  1. Go to Data Series options by right clicking on the Bars and clicking Format Data Series...

  2. Go to Fill Options and select Stretch, Stack or Stack and Stretch with. That last options requires you specify the Units/Pictures ration. In our case 1000$ should equal to 1 picture, so I input 1000

Too make the Image look even better, you may also want to go to SERIES OPTIONS and adjust the Gap Width!
The Final Effect looks more like this:
final excel pictograph

vba alerts and notifications

VBA Status Bar, Progress Bar, Sounds and Emails – Alerts in VBA

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Some VBA Macros run for ages – be it minutes or even hours. If we optimized our VBA Macro to the fullest there is often not much more you can do to change that. Usually what is irritating is waiting for VBA Macro code completion. Sitting by your desk is a waste of time – why not tell Excel/Access to let you know when the code has completed? Today we will explore such tools as the VBA Status Bar, VBA Progress Bar, Sound Notifications in VBA and Sending Email Alerts from VBA.

VBA Progress Bar

vba progress barProgress Bars are ubiquitous anywhere anything takes more than a couple of seconds to complete. So why doesn’t VBA have a native Progress Bar – beats me.

To add a VBA Progress Bar to your Visual Basic for Applications macro read my post here.

Excel Status Bar

vba status barThe VBA Status Bar is a panel that appears at the bottom of your Excel (or Access) Workbook. It is basically a Text Box to which you can display any non-blocking (non-modal) Message to your users.

Showing a Message in the VBA Status Bar

To show a message in the VBA Status Bar we need to Enable it using Application.DisplayStatusBar:

Hiding the VBA Status Bar

The VBA Status Bar when displayed, will remain displayed until ordered otherwise. It is best to hide and clean it after code execution completion:

Sound Notifications in VBA

What if you are away from your Screen, getting a Coffee or simply chatting with a colleague or getting on with our tasks stressed that we might miss that moment when our VBA Macro completes its task. Well why not introduce a Sound Notification? Let Excel or Access run a Sound to inform you that your Visual Basic for Applications macro has finally completed.

Playing a Sound in VBA

Playing a Sound in VBA is easy. Just add the below code snippet to a VBA Module:

It will play the Chimes.wav sound Once.

What if you don’t hear it? Running it once might obviously be not enough…

VBA Sound Alarm

The best approach is to create a VBA Sound Alarm that will run in a loop until we Turn it Off manually. What we will need is a Non-Modal UserForm as a pop-up to ask to stop the alarm.

First the macro code:

What happens here?:

  • I am measuring the execution time with the startTime and execTime variables
  • I am using the stopPlaying global VBA Variable to Turn Off the Alarm from our Alarm UserForm changes this value to True
  • I am displaying the AlarmForm with the Turn Off Alarm button

And now our AlarmForm code:

The result:
turn off vba sound notification

VBA Send Email Notification

Lastly when we are on the run and leaving our Workstation to process our VBA Macro remotely we might appreciate an email from Excel saying: Hey there! I just completed running the Macro!

You can find the code for the SendEmailFromOutlook function in my post here.

We might want to spice things up with maybe sending also the time it took to complete the macro:

Cool right? Makes the VBA Message Box hide in shame doesn’t it? The email should look like this:

vba email notification
VBA Email Notification

how to find duplicates in excel

How to Find Duplicates in Excel. Remove Duplicates in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

How to find duplicates in Excel? Today we will explore this question in and out. We will start first by understanding How to Highlight Duplicates in Excel. Our second objective will be learning How to Remove Duplicates. I will explore several approaches to this task – using the Data ribbons Remove Duplicates feature, but also showing how we can use MS Queries to remove duplicates and also do additional filtering/sanitizing of our data set.

CONTENTS

Find Duplicates in Excel

find duplicates source table
Example Table with Duplicates
There are several ways to go around Finding or Highlighting duplicates in Excel:

Following today’s post we will use the example Excel Table on the right.

1. Find Duplicates using Excel Formulas

To Find Duplicates in Excel using Formulas we will use the COUNTIF Excel formula to verify if a certain row as been repeated above. This will work only on a single column (our unique column identifier). Scroll down to the end to learn how to check for duplicates based on all three columns.

Add Is Duplicate? Column to your Worksheet

Add a Is Duplicate? Column to your Worksheet. In our example assuming that the Last column uniquely identifies records in my table input the following formula in the first cell of your new Is Duplicate? Column:

find duplicates in excel using formulas
Add COUNTIF Excel Formula to Find Duplicates

Excel Formula:

=IF(COUNTIF(B$1:B1;"="&B2);"DUPLICATE";"")

Drag the Formula down to all Cells

Drag the COUNTIF Formula from the First Cell down to the Last.
find duplicates in excel the steps

Instead of dragging the Formula down manually you can:

  1. Select the First Cell with the Formula
  2. Copy by Formula by hitting CTRL+C
  3. Select all cells down by hitting CTRL+
  4. Paste the Formula by hitting CTRL+P

Find Entire Row Duplicates

Did you notice that in the previous example 2 rows were in fact not duplicates? See below:

find duplicates in excel for an entire row
False Duplicates as Last name is not unique!

What should you do if there is no Column that Uniquely identifies your rows? We need to use a Helper Column! See Column D below:
find duplicates in excel with a helper column
Add a Helper Column that Concatenates All Columns

Simply add the Helper Column and Concatenate all other Columns like so:

=A2&"_"&B2&"_"&C2

Next you need to use the COUNTIF Formula on the Helper Column instead.

I recommend separating columns with some Separator (such as the _) as otherwise you may again get False Positive Duplicates

2. Highlight Duplicates using Conditional Formatting

highlight duplicates conditional formatting
Conditional Formatting in HOME Ribbon Tab
To Highlight Duplicates in Excel we need to use the Conditional Formatting feature in the HOME Ribbon Tab in Excel. Follow the steps below:
highlight duplicates conditional formatting
How to Highlight Duplicates in Excel using Conditional Formatting

Select the Column to Highlight Duplicates

highlight duplicates select columnSelect the Column in which you want to Highlight your Duplicates. As a Tip you can Select the First Cell and hit CTRL+END.

Open Conditional Formatting

highlight duplicates conditional formattingGo to the HOME Excel Ribbon Tab and select Conditional Formatting.

Create New Rule

highlight duplicates create new ruleAs we want to Highlight Duplicate Cell Values in Excel we need to create a new Conditional Formatting Rule. Click New Rule.

Select Duplicate formatting

highlight duplicates formatting
Select the Format only unique or duplicate values option to define the Formatting of Duplicate Values.

Select Duplicate formatting

In the new window make sure Format All is set to duplicate. Next set the formatting for your Duplicates.
highlight duplicates format
Once completed you should see your Duplicate Cells Highlighted in your Excel spreadsheet.
highlight duplicates in excel

Remove Duplicates in Excel

Having answered the question of How to Find Duplicates in Excel, but what you often want to do is Remove Duplicates. So lets now tackle the issue of How to Remove Duplicates in Excel.

Similarly as with Finding Duplicates, there several ways to go around Removing Duplicates:

Remove Duplicates with Data Ribbon

Fortunately Removing Duplicates is a typical scenario in Excel supported by the Data Ribbon Tab.
remove duplicates in excel

Select Excel Table

remove duplicates select tableSelect your Entire Excel Table by hitting CTRL+A.

Remove Duplicates

remove duplicates data ribbonGo to the Data Ribbon Tab in Excel and select Remove Duplicates.

Select Columns with Duplicates

remove duplicates select columns with duplicatesIn this last step you need to Select All Columns in which there are Duplicates. If you just want to remove rows in which there are duplicates in a single column – remember to select only that Column.
Once you finish hit OK and done!

Remove Duplicates with MS Query

Microsoft Query is a great tool to run MS Queries (SQL) on Excel data.

If you haven’t use MS Queries read my post on How to Create an MS Query in Excel or check-out my Excel SQL AddIn.

The DISTINCT SQL statement filters only the DISTINCT rows within a TABLE. You can use it on any SQL SELECT Query. See my example below:
remove duplicates ms query
The table on the Left is the Source Table the table on Right is the Result Table. This is the Microsoft Query you can use to filter only DISTINCT rows:

Where replace Sheet1 with the name of your Worksheet.

Count Duplicates in Excel

Similarly as with the previous case, there several ways to go around Counting Duplicates:

Count Duplicates using Excel Formulas

count duplicates in excelTo Count Duplicates in Excel follow the same steps as in Find Duplicates using Excel Formulas except you need to use the following Excel Formula using COUNTIF for the example in this post:

Similarly as in Find Entire Row Duplicates using Excel Formulas to Count Entire Rows that are duplicates, follow the above steps and create a similar Helper Column as such:

Count Duplicates using MS Query

Microsoft Query is a great tool to run MS Queries (SQL) on Excel data.

If you haven’t use MS Queries read my post on How to Create an MS Query in Excel or check-out my Excel SQL AddIn.

We can easily use a Microsoft Query to count only the duplicate records within our Data Set:
count duplicates in excel ms query
Here is the MS Query for the above Table:

vba last row featured

Excel VBA Last Row, Last Column, Last Cell

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Today I am going to take on one of the most frequent question people ask about Excel VBA – how to the the last row, column or cell of a spreadsheet using VBA. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Therefore I will be careful to explain the differences and nuisances in our quest to find the last row, column or cell in and Excel spreadsheet.

CONTENTS

VBA Last Row

The Last Row may as be interpreted as:

Last Row in a Column

vba last row
Last Row in a Column

To get the Last Row with data in a Column we need to use the End property of an Excel VBA Range.

Dim lastRow as Range

'Get Last Row with Data in Column
Debug.Print Range("A1").End(xlDown).Row 'Result: 5
Set lastRow = Range("A1").End(xlDown).EntireRow

'Get Last Cell with Data in Row
Dim lastRow as Range
Set lastRow = Range("A1").End(xlDown)

Last Row with Data in Worksheet

vba last used row
Last Used Row with Data in Worksheet

To get the Last Row with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Row with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Row
Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow 

'Get Last Row with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
Set lastRow = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireRow

Last Row in Worksheet UsedRange

vba last used row usedrange
Last Used Row in Worksheet UsedRange

To get the Last Row in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Row in Worksheet UsedRange
Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
Set lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).EntireRow
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Column

The Last Column may as be interpreted as:

Last Column with Data in a Row

vba last column
Last Column in Row

To get the Last Column with data in a Row we need to use the End property of an Excel VBA Range.

Dim lastColumn as Range

'Get Last Column with Data in Row
Debug.Print Range("A1").End(xlRight).Row 'Result: 5
Set lastColumn = Range("A1").End(xlRight).EntireColumn

'Get Last Cell with Data in Column
Dim lastColumn as Range
Set lastColumn = Range("A1").End(xlRight)

Last Column with Data in Worksheet

vba last used column
Last Column with Data in a Worksheet

To get the Last Column with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Column with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Column
Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn 

'Get Last Column with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireColumn

Last Column in Worksheet UsedRange

vba last used column usedrange
Last Used Column in Worksheet UsedRange

To get the Last Column in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Column in Worksheet UsedRange
Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
Set lastColumn = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).EntireColumn
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Cell

The Last Cell may as be interpreted as:

Last Cell in a series of data

vba last cell in data series
Last Cell in data series

To get the Last Cell in a series of data (table with non-blank values) we need to use the End property of an Excel VBA Range.

Dim lastCell as Range

'Get Last Cell in a series of data
Dim lastCell as Range
Set lastCell = Range("A1").End(xlRight).End(xlDown)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cells with Data in Worksheet

vba last cell in worksheet
Last Cell with data in Worksheet

To get the Last Cell with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Cell with Data in Worksheet using SpecialCells
Set lastCell = ws.Cells.SpecialCells(xlCellTypeLastCell)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

'Get Last Cell with Data in Worksheet using Find
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cell in Worksheet UsedRange

vba last cell in usedrange
Last Cell in UsedRange

To get the Last Cell in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Cell in Worksheet UsedRange
Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count,ws.UsedRange.Columns.Count)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA UsedRange

vba usedrange
The UsedRange in a Worksheet with not Values or Formulas

The VBA UsedRange represents the area reserved and saved by Excel as the currently used Range on and Excel Worksheet. The UsedRange constantly expands the moment you modify in any way a cell outside of the previously Used Range of your Worksheet.

The UsedRange is not reduced if you Clear the Contents of Range. The only way to reduce a UsedRange is to delete the unused rows and columns.

How to check the UsedRange

The easiest way to check the currently UsedRange in an Excel Worksheet is to select a cell (best A1) and hitting the following key combination: CTRL+SHIFT+END. The highlighted Range starts at the cell you selected and ends with the last cell in the current UsedRange.

Often bloated, large Excel files are effects of misuse of UsedRange. If you want to know more about reducing Excel file size read my post

Check UsedRange in VBA

Use the code below to check the area of the UsedRange in VBA:

Dim lastCell As Range, firstCell As Range, ws As Worksheet
Set ws = ActiveSheet
    
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column
Debug.Print "Last Cell in UsedRange. Row: " & lastCell.Row & ", Column: " & lastCell.Column    

For the screen above the result will be:

First Cell in UsedRange; Row: 2, Column: 2
Last Cell in UsedRange; Row: 5, Column: 6

First UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim firstCell as Range
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column

Last UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim lastCell as Range
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Debug.Print "Last Cell in UsedRange; Row: " & lastCell.Row & ", Column: " & lastCell.Column