Trending March 2024 # How To Freeze Rows In Excel? # Suggested April 2024 # Top 7 Popular

You are reading the article How To Freeze Rows In Excel? updated in March 2024 on the website Cancandonuts.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested April 2024 How To Freeze Rows In Excel?

Freeze Rows in Excel

In Excel, we have a function for freezing the rows as well. With the help of Freeze Row, we can fix the pane or row we select to go beyond the certain limit worksheet. Freeze Row can be accessed from the View menu tab’stab’s Window section from the drop-down list of Freeze Panes. First, to freeze the column, select the column we want to freeze, put the cursor anywhere in that column, and then select the Freeze Column option from the list. We will be able to see the selected column is now frozen.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Here we have 3 options. Freeze Panes, Freeze Top Row, and Freeze Top Column. I will show you examples of Freeze Rows in this article.

How to Freeze Rows in Excel?

We use Freeze Panes in Excel to keep an area or heading of a worksheet visible while scrolling to another part of a worksheet. This is very useful and convenient when working on a large table. So we select Freeze Panes from the View tab and keyboard shortcuts to lock that area.

You can download this Freeze Rows Excel Template here – Freeze Rows Excel Template

Let’s understand how to Freeze Panes in Excel with some examples.

Freeze or Lock Top Row – Example #1

Usually, our header is located in each column, i.e., horizontally. When scrolling down, we need to lock or freeze our first or top row, enabling us to see the first or top row at any given time.

Now look at the below image of sales data in the sheet.

It isn’t easy to see all the headers when scrolling down. So, in Excel, we have an option called Freeze Top Row, which holds onto the top row when scrolling down and helps us see the heading all the time.

Let’s look at the below steps to understand the method.

Step 1: Select the worksheet where you want to freeze your top row.

Step 3: Ok, done. It is as easy as you eat pizza. You have frozen your top row to see the top row while scrolling down.

Even though I am in the 281st row, I can still see my headers.

Freeze or Lock Multiple Rows – Example #2

We have seen how to freeze the top row in the Excel worksheet. I am sure you found it a walk-in-the-park process; you did not have to do anything special to freeze your top row. But we can freeze multiple rows as well. So here, you need to apply simple logic to freeze multiple rows in Excel.

Step 1: Identify how many rows you need to freeze in the Excel worksheet. For an example, take the same data from the above example.

Place a cursor on the A8 cell. This means I want to see all the rows above the 8th row.

Step 2: Remember we are not only freezing the top row, but we are freezing multiple rows at a time. Do not press ALT + W + F + R in a hurry; hold on momentarily.

After selecting cell A8 under freeze panes, again select the option Freeze Panes under that.

Now we can see a tiny grey straight line below the 7th row. This means the above rows are locked or frozen.

You can keep seeing all 7 rows while scrolling down.

Things to Remember

We can freeze the middle row of the Excel worksheet as your top row.

Make sure the filter is removed while freezing multiple rows at a time.

If you place a cursor in the unknown cell and freeze multiple rows, then you may go wrong in freezing. Make sure you have selected the right cell to freeze.

Recommended Articles

This has been a guide to Freeze Rows in Excel. Here we discussed How to Freeze Rows in Excel and different methods and shortcuts to Freeze Rows in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles to learn more –

You're reading How To Freeze Rows In Excel?

How To Use Calendar In Excel?

Calendar in Excel

Excel Calendar is a customized calendar in which we can create our calendar with the help of a template available. The main problem comes when we must decide what the first day of the year will be. For the Year 2023, we all know that the first day started on Wednesday. To decide the day of 1st Jan, use the DATE function to find the first date of the month and then subtract the 1st Weekday Date using the WEEKDAY function along with DATE.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Excel provides a variety of different templates which is already formatted and ready to use.

How to use the pre-built calendar in Excel?

To create a pre-built calendar in Excel, follow the below process:

You can download this Calendar Excel Template here – Calendar Excel Template

Go to the File tab.

It will open several options in the right side window. Refer to the below screenshot.

In this window, we have to enter what we are looking for in the search box.

Type Calendar in the box and press Enter key like the below screenshot.

It will display various types of calendar options, as shown below.

It has many options like monthly and yearly calendar formats.

Scroll the bar and choose the Photo calendar (Mon) option. Refer to the below screenshot.

It will open an Excel workbook having a month-wise date calendar.

 Here month-wise tab is available, as shown in the screenshot below.

It will look like the below screenshot.

We can format this calendar by changing pics as per our choice.

You can change the address and contact details in the box available on the right side. Refer to the below screenshot.

Like in the below screenshot, we have mentioned some notes date-wise.

You can customize the calendar using the format tab as per your requirement.

A calendar in Excel is very important in any company, and it uses for different purposes. Here we are taking an example where we will see a way to create a calendar in Excel for a team.

Use Custom Calendar sheet in Excel.

Let’s consider two employee data in a calendar in the Excel sheet. David & Ian are two employees in a team, as shown below:

Thus, we are creating a monthly Excel calendar for them. Let’s write the first date of the Jan 19 month in Cell B1. Now, in Cell C1, we will apply the formula =B1+1 for the next date, as shown in the screenshot below.

Hit the enter key and drag this formula in the next columns for the last day of the month. Here drag till the 31st Jan 19.

Copy the same dates in Row 2. Refer to the below screenshot.

It will open a drop-down list of items.

Choose the Custom option from the Category window and write “ddd” under the Type field for Weekdays. “ddd” refers to Mon, Tue, etc. Refer to the below screenshot.

It will convert the dates into weekdays in Row 1.

Again select the whole dates in Row 2 and write “dd” in the Type field under the Custom option. It will convert the dates into days, as shown in the below screenshot.

Select the columns from B to AF and move the cursor on the line between any two columns, and it will compress the cells as shown in the below screenshot.

And our calendar will look like below:

Now our Template is ready. Make a copy of this sheet and give the month name of the sheets, like Jan 2023, Feb 2023, etc. Put the name of this sheet as Template.

For Feb 2023 month, we need to change the date of months like below.

As we can see, the calendar template is ready with us, and now we can use this for scheduling team employee activities date and month-wise.

How to Print a customized Calendar in Excel?

In the above section, we learned how to create a calendar. We also can take the print of this. Follow below steps for printing a customized calendar:

Step 1 – Go to the Page Layout tab.

Step 4 – Choose 1 page under Width and Height boxes in the Scale to Fit, As shown in the screenshot below.

Now your customized calendar is ready to print on one page.

Things to Remember 

Using this Excel Calendar, you can easily keep track of daily, weekly, and monthly activities, schedules, etc.

You can keep track of your business growth by watching the progress on a fixed-date timeline.

You can keep real-time watch and visibility into the business’s status, resources, and performance.

Recommended Articles

This has been a guide to Calendar in Excel. Here we discuss how to use a pre-built calendar in Excel, a Custom calendar, and print the customized calendar along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

How To Hide Negative Numbers In Excel?

In Excel, the user will understand the concept of how to hide negative numbers in Excel. Equations can also be written using integers with negative values. In this article, the user will learn three common examples. The first example is based on negative numbers by using conditional formatting. The second example is based on hiding the negative number by using the format cell option. Both examples are explained thoroughly within the article.

Example 1: To hide the negative numbers in Excel by using the conditional formatting Step 1 Step 2

Go to the Home Tab under the Styles group and select the Conditional Formatting arrow as highlighted below image −

Step 3

After select the conditional formatting’s drop menu. a menu list will appear as highlighted in the below image −

Step 4 Step 5 Step 6

In Format cells that are LESS THAN: text box enter 0.

Step 7

Now press the drop down arrow provided on the side of the “with” text box and select Custom Format..

Step 8

This will open a Format cells dialog box. This dialog box, comprises four tabs as display in the below image −

Step 9

To perform the required task, choose the Font Tab and in Color section opt for white color, this will modify the font color of negative number to white, and the numbers can become invisible on work sheet.

Step 10

Now select the OK button that will close the dialog box named Format Cells.

Step 11

Again, select the highlighted button named OK“Less than” dialog box.

Consider the final provided output snapshot below. All the negative values are now become invisible due to the use of white color font.

Example 2: To hide the negative numbers in Excel by using the format cell option Step 1

Again, consider the same worksheet. Firstly select the cell range by using the negative numbers. Consider the below image for reference −

Step 2 Step 3

From the list of appeared option, select “Format Cells…”.

Step 4

This will finally, display a “Format Cells” dialog box. This dialog box, contains many option tabs such as numbers, alignment, and many other.

Step 5

Go to the Number Tab, and from the Category section select the “Custom” option.

Step 6

Move to the “Type:” section and in the provided textbox enter “0; ”. To extract all the negative values, that is number less than 0.

Step 7

Now in the dialog box named Format Cells, close this dialog box by pressing the OK button.

Step 8

The above step will replace all the negative values with space. Consider below provided screenshot for proper reference −

Example 3: To hide the negative numbers in Excel by using the kutools Step 1

Consider the below given worksheet. This worksheet contains 9 entries numeric entries.

Step 2 Step 3 Step 4 Step 5

Consider snapshot of selected data cells −

Step 6

Again select the “Home” tab, and under the “Font” section, select the last option, and select the “White” color as provided below −

Step 7

This step will hide all the negative values by changing their font to white color.

Conclusion

This article focusses on guiding about the three different ways, by using which use can hide the negative numbers in excel. All the provided stepwise data explanation is accurate and precise. In the first example, the conditional formatting would be used to resolve the given problem statement. Format cell option is used in the second example. Kutools option is depicted in the third example to hide the negative number.

How To Perform Spell Check In Excel (Examples)

Spell Check in Excel (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Introduction to Spell Check in Excel Uses of Spell Check in Excel

Spell Check is used to identify spelling errors, typing errors, or any kind of duplicate words (I will explain it with an example) in Excel. It may be a useful tool when using some texts repetitively in Excel. Although many of us play with numbers in Excel, spell check is crucial in creating some reports and datasets that may have text. So, nobody will want to see any errors in it, or you don’t want to upset your managers/bosses with these mistakes.

Examples of Spell Check in Excel

In the below screenshot, I have the conversation between 2 guys saved in Excel. Don’t go with the content; it’s purely to make you understand better.

Screenshot 1

In Screenshot 1, you can see there are a lot of typing errors and spelling mistakes.

To run spell check in this particular workbook, you must activate the cells where you want to check spelling or typing errors and go to the “REVIEW” tab in the Excel ribbon, and there you will find the spell check option.

A shortcut to run spell-check is simply pressing the “F7” button from your keyboard after activating the cells/worksheet.

Screenshot 2

In the above Screenshot 2, you can notice that I have selected the A1 cell and the spell check option in the review tab.

Key Point: If you select cell A1, it will go through all the cells horizontally, i.e., all columns of the first row, then move to the second row and check all the column cells in the second row (from left to right) and then move to the third row and so on.

A spelling dialogue box will be displayed after going on to the last cell, where any value is stored and identifies any spelling error.

If you want to start to spell check from somewhere else, let’s take an example, cell D5, it will simply go through all cells of Row 5 and then rows below it, and when done will all rows after D5, it will ask you, “if you want to continue checking at the beginning of the sheet?”. Some dialogue boxes like the below screenshot will appear. If you select yes, it will check the remaining cells (A1 to D5 in our case) and shows suggestion. To make you understand better, I have selected the D5 cell and pressed F7 now; it will check all the cells after it, give spelling correction suggestions & after it will ask you to check the remaining cells or not and work accordingly. For your better understanding, please go through the below Screenshots.

Screenshot 3

Pressed F7 after activating B13 cell, now spell check dialogue box will appear with correction suggestions.

Screenshot 4

The results of the cell B13 correction through Spell Check is mentioned in the screenshot.

Screenshot 5

After completing the last cell with correction(C13), it has asked to check from the beginning of the sheet. As discussed above.

Features of Spell Check in Excel Dialog Box

You might have noticed in the screenshots that many spell-check options are available. Let us discuss them one by one.

Add to Dictionary: If there is an error in any text, but you anticipate using the word frequently in the future (such as a name or abbreviation), you can add it to the dictionary in Excel. It will become part of the dictionary and a list of acceptable words. Any word added to Excel’s Dictionary will not be detected as an error in the future in any workbook.

Change: you can use it when you want to change a word according to suggestions, and it will change it according to the selected suggested word. (You can see in Screenshot 3 the words suggested for text in cell C5.)

Change all: It will replace all the instances of that word in a worksheet according to the selected suggestion.

Autocorrect: If you go with this option, it will autocorrect the word with a selected suggestion, and it will be added to the autocorrect list viz; every time you type that word, it will be automatically corrected according to the selected suggestion.

Dictionary Language: We can change the dictionary used in Excel.

Options: Used to change the Excel default settings (discussed later in this guide), and it will open an option box where you can make changes accordingly.

Now I chose to continue to spell check from the beginning of the sheet. And chose options accordingly. All spell checks are done.

In the above Screenshot 6, you might have noticed some spelling errors in the worksheet, but Excel isn’t detecting them. Have you noticed? It has brought us to notice the default settings of the Spellcheck, and they are:

It ignores all errors in the upper case. For e.g., the words “AAM” and “FIINE” in cells C4 & D4 are not marked as errors.

All words with numbers in them will be ignored too. (cell B12 “newww123”)

It also ignores all internet addresses and file addresses. (D7 & B12)

Screenshot 7

Another important feature to note here is Spell check doesn’t ignore repeated words. You can identify it by comparing Screenshot 1 and Screenshot 6 carefully.

Cell F4 has “how are you?” and spell check has detected it. I have deleted it with the delete option, which only appears when we have any kind of repetition in Excel.

Things to Remember

Excel uses spell check to identify spelling errors.

It detects the repetition of words.

You can ignore, change, autocorrect, or add to the dictionary all detected errors according to your requirement.

It doesn’t detect errors in words written in uppercase or have numbers in it.

It ignores all file and internet addresses.

Recommended Articles

This is a guide to Spell Check in Excel. Here we discuss Spell Check in Excel and How to use Spell Check in Excel, along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

How To Automate An Excel Sheet In Python?

Approaches

Using the openpyxl library

Using the pandas library

Using the xlwings library

Method 1: Using the openpyxl library

We can use the Python Openpyxl package for interacting with Excel files. This library allows us to read, write, and edit Excel files.

We can install openpyxl module using the following command:

pip install openpyxl

Suppose there is an excel file with the following contents −

Now, we want to automatically add the data in the new field for storing bonus+ salary of a particular employee. For this, we will write some code based on the below algorithm.

Algorithm

Import the necessary modules

Load the Excel workbook ‘python_worksheet.xlsx’ using the openpyxl.load_workbook() function and assign it to the variable ‘wb’.

Select the first sheet of the workbook using wb[‘Sheet1’] and assign it to the variable named ‘sheet’.

Loop through each row in the sheet starting from the second row:

Access the cell in the second column of the current row using sheet.cell(row, 2) and assign it to the variable ‘cell’.

Access the cell in the third column of the current row using sheet.cell(row, 3) and assign it to the variable ‘salary_bonus_cell’.

Add 500 to the value of ‘cell’ using int(cell.value) + 500 and assign the result to the value of ‘salary_bonus_cell’ using salary_bonus_cell.value = int(cell.value) + 500.

Step 1 − Import necessary modules

import openpyxl as xl from openpyxl.chart import BarChart, Reference

Step 2 − Load the workbook and select the worksheet

wb = xl.load_workbook('python_worksheet.xlsx') sheet = wb['Sheet1']

Step 3 − Loop through each row starting from the second row and perform the calculations as per need.

for row in range(2, sheet.max_row + 1): cell = sheet.cell(row, 2) salary_bonus_cell = sheet.cell(row, 3) salary_bonus_cell.value = int(cell.value) + 500

Step 4 − Save the modified workbook with a new filename

Example # Importing the necessary modules import openpyxl as xl # Loading the workbook and selecting the sheet wb = xl.load_workbook('python_worksheet.xlsx') sheet = wb['Sheet1'] # Looping through each row of the sheet starting from row 2 for row in range(2, sheet.max_row + 1): # Accessing the cell in the second column of the current row cell = sheet.cell(row, 2) # Accessing the cell in the third column of the current row salary_bonus_cell = sheet.cell(row, 3) # Adding 500 to the value in the cell from column 2 and updating the value in column 3 salary_bonus_cell.value = int(cell.value) + 500 # Saving the modified workbook with a new filename

The openpyxl method involves reading straight from the Excel files and writing to Excel files using the openpyxl module. We got to know how to open an Excel file, edit its data and make new column values. While this method can be used to work with single cells as well as ranges of cells, the code can be too lengthy and less effective for bigger datasets than the second method due to some limitations of this library.

Output Method 2: Using the pandas library

Pandas is a Python package mostly utilized for data analysis. It also offers the ability to read and write Excel files, though. An illustration of how to automate an Excel sheet using pandas is given below:

We can install pandas in python using the following command:

pip install pandas

Suppose there is an excel file with the following contents:

Now, we want to do the same operation as described in the last example, we will be following the below algorithm:

Algorithm

Import the pandas library as pd.

Use the pd.read_excel() method to read the data from an Excel file.

Specify the sheet name “Sheet1” to read from the Excel file.

Manipulate the data as needed using the various methods available in pandas.

Create a new column in the DataFrame named “Bonus” that adds 1000 to the “Salary” column.

Use the to_excel() method to write the updated data back to a new Excel file and also Specify index=False to prevent pandas from writing the DataFrame’s index to the output file.

Step 1 − Import the pandas library using the statement “import pandas as pd”.

import pandas as pd

Step 2 − Read the input Excel file “python_worksheet.xlsx” using the pd.read_excel() method and store it in a variable called “data”.

data = pd.read_excel('python_worksheet.xlsx')

Step 3 − Specify the sheet name as “Sheet1” in the pd.read_excel() method to read data from the Sheet1 worksheet of the input Excel file.

data = pd.read_excel('python_worksheet.xlsx', sheet_name='Sheet1')

Step 4 − Create a new column in the “data” DataFrame called “Bonus” by adding 1000 to the existing “Salary” column. You can do this using the “+” operator and saving the result in a new column.

data['Bonus'] = data['Salary'] + 1000 Example # Import the pandas library import pandas as pd # Read the input Excel file "python_worksheet.xlsx" into a DataFrame called "data" # and specify the sheet name as "Sheet1" data = pd.read_excel('python_worksheet.xlsx', sheet_name='Sheet1') # Create a new column in the "data" DataFrame called "Bonus" by adding 1000 to the existing "Salary" column data['Bonus'] = data['Salary'] + 1000 # Pass the DataFrame "data" as the first argument and specify index=False to prevent the DataFrame's index from being written to the output file

The pandas method involves reading and writing data in Excel files by using DataFrames. In our example, we showed how to take data from an Excel file, automate adding a new column, and then putting all the new data to another Excel file. As pandas provides a wide collection of capabilities for manipulating, cleaning, and analysis of data, this method is helpful even when working with huge datasets.

Output

In this example, we have automated the excel sheet for populating the Bonus column using python

Method 3: Using the xlwings library

Xlwings is a python library that gives the users a way to communicate and automate processes with Microsoft Excel from Python. It enables users to automate Excel activities like creating and editing Excel charts and tables using Python, as well as write Excel formulas and functions.

Also, we can do calculations and analysis on Excel data using Python functions and libraries, and then write the findings back into Excel.

We can install xlwings in python using the following command:

pip install xlwings

Suppose there is an excel file with the following contents −

Now, we want to do the same operation as described in the last example, we will be following the below algorithm:

Algorithm

Import the xlwings module.

Load the Excel file into an xlwings Workbook object.

Select the Worksheet and determine the last row of the ‘Salary’ column.

Add 500 to each value in the ‘Salary’ column to create a new ‘Bonus’ column.

Write the result to the corresponding ‘Bonus’ cell.

Save the modified workbook with a new filename.

Close the workbook

Step 1 − Import the xlwings module.

import xlwings as xw

Step 2 − Load the Excel file into an xlwings Workbook object

wb = xw.Book('python_worksheet.xlsx')

Step 3 − Select the Worksheet and add 500 to the ‘Salary’ column to create a new ‘Bonus’ column

sheet = wb.sheets['Sheet1'] last_row = sheet.range('B' + str(sheet.cells.last_cell.row)).end('up').row sheet.range('C2:C' + str(last_row)).value = [[cell.value + 500] for cell in sheet.range('B2:B' + str(last_row))]

Step 4 − Save the modified workbook with a new filename

Step 5 − Close the workbook

wb.close() Example # Import the xlwings module import xlwings as xw # Load the Excel file into an xlwings Workbook object wb = xw.Book('python_worksheet.xlsx') # Select the Worksheet and add 500 to the 'Salary' column to create a new 'Bonus' column sheet = wb.sheets['Sheet1'] last_row = sheet.range('B' + str(sheet.cells.last_cell.row)).end('up').row sheet.range('C2:C' + str(last_row)).value = [[cell.value + 500] for cell in sheet.range('B2:B' + str(last_row))] # Save the modified workbook with a new filename # Close the workbook wb.close()

This approach involves using the xlwings library, which allows you to automate and manipulate Excel through Python code. Due to this library’s speciality of working with Excel files, we can make use of this approach easily for automating our large excel tasks.

Output

We have successfully added a column for Salary+Bonus in our excel sheet

Conclusion

Automating Excel sheets using Python can be a useful tool for handling huge datasets, streamlining redundant and repetitive tasks, and producing larger reports. We’ve covered three popular methods in this article for automating Excel sheets in Python. Ultimately, the method you choose will rely on the requirements of the tasks as well as the volume and complexity of the data. Automating Excel sheets in Python can help you save time, cut down on manual errors, and boost productivity if you use the correct strategy and equipment.

How To Use Vba Trim Function To Remove Spaces In Excel?

Excel VBA Trim Function

Excel VBA Trim Function is used for removing the extra spaces from any cell or text and gives us the output which has a standard in terms of required spaces. VBA Trim function works exactly as the Excel Trim function and Trim function also removes the extra spaces in 3 ways;

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Spaces from the starting of the text.

Spaces from the end of the text.

Spaces from the middle of the text if more than 1 extra space is seen.

This mostly happens when we download data from some server, data warehouse or while incorrectly inserting the extra spaces manually. Even though we can easily see the spaces at the middle of the text, but spaces at the beginning and end of text cannot be seen easily until and unless we go to edit mode of that specific cell. This can be done by VBA Marco.

How to Use Excel VBA Trim Function?

We will discuss how to use VBA Trim Function by using some examples.

You can download this VBA Trim Function Excel Template here – VBA Trim Function Excel Template

VBA Trim Function – Example #1

Here we have 3 cells in the below screenshot.

And each cell has some spaces associated with them. Cell A1 has spaces at the end.

Cell A2 has spaces at the beginning of the text.

And cell A3 has space in between the text which is highlighted in the below screenshot.

This will take us to Visual Basic coding window. Now go to the Insert menu from VBA window and select Module as shown below.

This will create a new Module where we will write a code for trimming. Below are the steps for creating and writing trimming code:

First, choose a variable and define a range. Here we have selected the variable “A”. This variable can be anything.

As our data set has already some text or object then we need to set the range for it. And for that, we will use “Selection”. It will automatically select the data in the present sheet.

Code:

Sub

Trim_Data()

Dim

A

As

Range

Set

A = Selection

End Sub

For selecting each filled cell of the sheet “For Each” is the function And Value function is used for selecting the value with Trim(Cell) selection.

Code:

Sub

Trim_Data()

Dim

A

As

Range

Set

A = Selection

For Each

cell

In

A cell.Value = WorksheetFunction.Trim(cell)

Next

End Sub

By this, we complete the coding work for creating macro through VBA Trim. Now let’s apply the created code into some tab. For this go to Insert Menu and under Illustrations select Shapes.

From that Shapes option, create any shape using shape formats and styles.

VBA Trim Function – Example #2

VBA Coding shown above can be written in one more way. In the above example, we only trimmed the selected data. In this we will insert the function in the message box as well it will show once the data is trimmed.

Once we do that, we will get the Visual Basic window. Now open a fresh Module and start coding on that page.

Now consider the same code which we have written in example-1. But here for printing a message box with the text we will insert some text.

Sub

Trim_Data2()

  Dim

A

As

Range

Set

A = Selection

For Each

cell

In

A cell.Value = WorksheetFunction.Trim(cell)

Dim

B

As

 

String

B = Trim("Trimming Done") MsgBox B

Next

End Sub

Once we are done with writing the code, close the Visual Basic windows.

After that, we will get the button created on the screen.

As we can see above, here our data got trimmed and we can see the message as well of “Trimming Done”.

Pros of Excel VBA Trim Function

We can trim huge sets of data in one shot without checking the number of extra spaces the cells have.

Coding of trimming function is also quite small.

There are very less or no chances that any extra space will get spared.

Things To Remember

Always save the file as Macro-Enabled Worksheet. By doing this, we can use assigned macro multiple time.

Compile the code before assigning it to any button.

There are 2 different ways are shown for creating a button and both can be used as per individuals need or wish.

Consider writing VBA Code as shown in example-2 which will give the message as well.

Recommended Articles

This has been a guide to Excel VBA Trim. Here we discussed how to use VBA Trim Function to remove spaces in Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles–

Update the detailed information about How To Freeze Rows In Excel? on the Cancandonuts.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!