Home » Macros

Delete Data, Keep Formulas with an Excel Macro

by —Leave a Comment

Hello, Excellers. Welcome back to another #Exceltip, #MacroMonday blog post in my 2021 series. This Excel tutorial will show you how to delete data but keep formula in Excel. Yes that is correct. This code will delete constants but keep your formulas.

I also have a tutorial on how to delete cell data without writing a macro. You can read the alternative version here.

Excel Macro

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor. There are two ways to do this.

  • Hitting ALT +F11 or
  • Select the Developer Tab | Code Group | Visual Basic.

Both methods have the same result.  It is just personal preference.

You then choose to create a module to store your code either in your What’s the difference?. If I save the macro in your Personal Macro workbook, it will be available for use in any of my Excel workbooks. However, if you store it in the current workbook, then use is restricted to that workbook. As I probably will reuse this code repeatedly, it makes sense to save the code in my Personal Macro Workbook.

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

delete data but keep formulas. An Excel macro.

Step 1. Name the Macro.

The first step after inserting a new module is to name the macro. Type Sub then the macro name. There are a few guidelines on macro names. If you need to review these guidelines, then read my blog post here. In this example I have named the macro ClearDataCells.

Sub ClearDataCells()

End Sub

Excel automatically inserts the End Sub code. Insert all other lines of code between these two lines.

Step 2. Using Special Cells

ActiveSheet.Cells.SpecialCells(xlCellTypeConstants).ClearContents

So, the second step and the only line of code is to use the Range.SpecialCells method. This aids in selecting cells with data. The syntax of Range.SpecialCells is below

expression.SpecialCells (Type, Value)

expression -A variable that represents a Range object.

The ActiveSheet.Cells give the Range Object. The Range.SpecialCells will allow the selection of cells with data using the xlCellTypeConstants parameter.

Step 3. End The Macro after deleting data but keping formulas.

End Sub

The final step in this code is to end the macro. The End Sub line of code already exists from the naming of the macro in the first line of code. So, this is an alternative way to delete data but keep formulas in Excel.

Further Recommended Reading.

Finally if you want even more Excel or Macro Tips then read my full list of Macro Monday and Formula Friday Blog Posts all in one place. Don’t forget to bookmark the links as I update them every week.

you tube how to excel at excel

Unmerge All Merged Cells In Your Excel Worksheet.

by —Leave a Comment

Hello Excellers, and welcome back to another #MacroMonday#Excel tip today. Do you know if there are merged cells in your Excel worksheet?. Do you want to remove or unmerge cells all at once?. Then a tiny piece of Excel VBA macro code will speed up your work in a flash. read on to find how to unmerge cells in Excel.

If you want to read more of my related blog posts on merged cells feel free to read my links below.

 

To Merge Or Not To Merge That is The Question

How To Find All Merged Cells In Your Worksheet

Table of contents

Preparing To Write The Code.

First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice. You can create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save your code in your Personal Macro workbook, it will be available for use in any of my Excel workbooks. If you store it in the current workbook, then use is restricted to that workbook.

As you can see this code will be useful to reuse in any workbook. I will create and save this for future use in my Personal Macro Workbook.

 

Learn More About Your Personal Macro Workbook (PMW)

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

 

Starting The Macro

how to unmerge cells in Excel

We need to start the macro by inserting a new module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it Sort_Worksheets. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We need to enter the rest of the code between these two lines.

Sub UnMerge_All_Cells

End sub

UnMerging All Merged Cells.

This routine is a really simple one. I did say it was. It is a one-line instruction to Excel to unmerge any merged cells on the Active Sheet. This restricts the code to work on only the current sheet the user is clicked into.

ActiveSheet.Cells.Unmerge

Ending The Macro.

Once all of the merged cells are unmerged, the code ends.

End Sub

Test Your Code. Unmerge Cells.

Now all you need to do is test your Excel Macro.  This is my favorite part!. So, that is how to quickly unmerge cells in Excel.

 

Excel Macro

Macro To List All Excel Worksheets In A Workbook.

by —1 Comment

Welcome to another simple #Excel macro in my #MacroMonday blog series. Today I want to show you how to write a simple Excel macro that will list all Excel worksheets in a workbook. This macro is a great time saver and valuable if you have a workbook but are unsure how many worksheets you have in it and what they are named. This macro will list all Visible, Hidden and Very Hidden worksheets in an Excel workbook.

Table of contents

Worksheet Visible Property.

Before we get into writing our simple bit of VBA code, let’s take a quick look and briefly clarify the different options available for worksheet visibility. Worksheets can have one of three visible properties.

  • Visible.  This property is the default, and if you can see a worksheet, then the property is visible.
  • Hidden.  The Excel user has flagged the worksheet as Hidden from the Excel interface. The user can right-click and select Hide from the pop-up menu or select the Home Tab | Cells Group | Format | Visibility | Hide & Unhide | Hide Sheet. This basic level of Hidden allows worksheets to be made visible or hidden from the worksheet interface easily.
Hide or UnHide Excel worksheets.
  • Very Hidden. This property of Very Hidden means that the worksheet cannot be seen to e available to Unhide in the usual pop up menu in the Excel interface. To see these worksheets again or set to worksheet property to Very Hidden, the user must use the VB Editor. I have included a brief explanation of how to do this below.

Use the VB Editor- Worksheets Very Hidden.

It is simple to flag worksheets as Very hidden as long as you have access to the VB Editor. First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. 

The worksheets in your Excel workbook are now visible. To change the status of a worksheet to very hidden, follow the simple steps below.

  • Select the worksheet to hide.
  • In the properties window of the VB editor, scroll to Visible
  • Select 2 – xlSheetVeryHidden

. 

So, back to writing our small VB macro to list all worksheets in our workbook.

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor.  There are two ways to do this.  Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.   You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook.  What’s the difference?.  If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook.

This macro will be useful in the future so I am going to save it in my Personal Macro Workbook.

More About Your Personal Macro Workbook (PMW).

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

List All Worksheet In An Excel Workbook with a macro

We need to start off the macro by inserting a New Module.  Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro.  In this example, I have called it simply ListMySheets.  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We need to enter the rest of the code between these two lines.

Sub ListMySheets
End Sub

Declaring Variables.

We need to declare two variables in the macro, which creates a memory container to store these values. The two variables are ws as Worksheet and x as Integer. We are also setting x as the value of 1.

Dim ws As Worksheet
Dim x as Integer
x= 1

Insert A New Worksheet.

We first insert a new worksheet at the beginning or before any other worksheets in the workbook. I like to place it here as it is like an index or contents page of the workbook. You can choose to place it after all worksheets by changing the Worksheets.Add(Before to Worksheets.Add(After in the VB code.

Worksheets.Add(Before:=Worksheets(1)).Name = "List Of Worksheets"

Use The For Next Loop To List Excel Worksheets.

We use the For Next Loop method to loop through each of the worksheets in the Excel workbook and enter the Worksheet’s name into the newly created ‘List Of Worksheets’. Then, Excel inserts the worksheet name in the cell reference of Cell(x, 1). This is the reference to Row 1 as we have declared that x = 1, and Column 1. Thus, every time Excel loops through each Worksheet, the Row value of 1 increases by 1. Subsequently, each worksheet name is placed in the next row cell down from the previous one.

For Each ws In Worksheets
     Sheets("List Of Worksheets").Cells(x, 1) = ws.Name
     x = x + 1
Next ws

Ending The Macro.

The routine then ends with the End Sub piece of code that was already entered into the module for us when we started to type the name of the Macro.

End Sub

Copy The Code

Sub ListMySheets
'Macro by HowToExcelAtExcel.com

Dim ws As Worksheet
Dim x as Integer
x= 1

Worksheets.Add(Before:=Worksheets(1)).Name = "List Of Worksheets"

For Each ws In Worksheets
     Sheets("List Of Worksheets").Cells(x, 1) = ws.Name
     x = x + 1
Next ws
End Sub
Excel Macro

Macro Mondays – How To Assign Or Change A Macro Shortcut Key

by

Hello Excellers, time for some back to basics today with Excel Macros.  Let’s take a look at assigning or changing a shortcut key with a Macro. Yes, welcome back to another #MacroMonday blog post in my Excel 2021 series.

The Basics Of Excel Macro Shortcut Key.

When you start recording a macro with the Macro Recorder, the Dialog Box appears, complete with a section or space for a Shortcut Key for the Macro.

See my example below.  I have assigned the keyboard shortcut Ctrl+Shift+T to my Macro called Macro1.

assign

So not only does your Keyboard Shortcut get displayed in the Macro Recorder  Dialog Box it is also visible in the Visual Basic Editor screen. How handy is that right?.

excel macro shortcut key

How To Change The Keyboard Shortcut Key Assigned To Your Macro.

If you decide to change the Shortcut Key, it is simple to do.

  • Choose Developer | Code | Macros (or press Alt+F8). The Macro dialog box appears
  • Select the Macro name from the list
  • Click the Options button. The Macro Options dialog box opens
  • Change the letter for the Shortcut Key
  • Hit Ok
assign or change a macro shortcut key

Do You Want To Learn More Cool Stuff?

If you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not bookmark it? Yes, it is updated EVERY Monday.

How To Excel At Excel – Macro Mondays Blog Posts.

Learn Excel With Our Excel Online Courses

If you want more Excel and VBA tips like this data validation one, then sign up for my monthly newsletter. I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Excel Macro

More Excel Tips.

A Function To Generate A Random String Set In Excel.

by

Hello Excellers, welcome back to another blog post in my #Excel #MacroMonday 2021 series. Today let’s write our own Excel macro function that will generate a random string of characters. This random string generator can be helpful to create unique passwords of any given length automatically at any time.

Table of contents

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.

You then choose to either create a module to store your code either in your Personal Macro Workbook or in your current workbook or create a UDF or User Defined Function as we are writing our Function or Formula.

What’s the difference?. If you save the macro in your Personal Macro workbook, it will be available for use in any of my Excel workbooks. If you store it in the current workbook, then use is restricted to that workbook. As we write a UDF or user-defined function, we can make it available as just another regular function or formula in Excel. We do this by saving it as an Exel Add-In.

I want to be able to use this macro at any time to generate a password. So I will save this as an Excel Add-In.

More About Your Personal Macro Workbook (PMW).

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Starting The Macro.

generate random string

Function Procedures.

So, in the way that every Sub procedure begins with the words Sub, Functions always start with the word Function and end with the phrase End Function. So, we begin to write our code by typing Function and the name we want to call your function. In this example, I want to call my function PasswordString.

Function PasswordString (Length As Integer)
End Function

Declaring Variables.

We now need to declare three variables for this Function. By declaring these variables, Excel creates memory containers for each of these values.

Dim Strings As Variant, x As Long, str As String

Message Box Warning.

We need the user to enter a value into the length argument of the function to be greater than zero. If the value is not greater than zero, a message box pops up to warn the user to enter the correct value to enter, a value greater than zero characters.

If Length <1 Then
    MsgBox "Length variable must be greater than 0"
    Exit Function
  End If

Specify The Array Values And Characters.

This next piece of code specifies the array of characters and values that Excel can use to generate the string for the password randomly. We declared at the beginning of the function that this value is a Variant. A variant is a particular data type that can contain any kind of data except fixed-length. Excel can therefore generate the random password string made of numbers and characters, precisely what we want in a password.

Strings = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", _
  "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", _
  "y", "z", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "!", "@", _
  "#", "$", "%", "^", "&amp;", "*", "A", "B", "C", "D", "E", "F", "G", "H", _
  "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", _
  "W", "X", "Y", "Z")

Selecting Characters Individually To Generate The Random String.

First, Excel sets the length of the generated password by using x=1 to length, which is the length of the password specified in the functions only argument.

For x = 1 To Length
    Randomize
    str = str &amp; Strings(Int((UBound(Strings) - LBound(Strings) + 1) * Rnd + LBound(Strings)))
  Next x

The Function Result. Generate A Random String!.

The result of the randomized string is then returned and displayed as a typical result in the Excel cell.

PasswordString = str

Ending The Function.

Excel generates the random string. At this point, the code and also the Function ends.

End Function

At last, it is now time to test the function. Great, it looks good. That is how to generate a random string in Excel. A great way to generate Excel passwords.

generate a random string in Excel for password

Finally, it is now time to make this Function available in any of my Excel workbooks. We do this by creating an Excel Add-In.

What Is An Excel Add-In?.

An Excel add-in is simply a File with an extension of .xlam or .xll. Files are loaded up when Excel starts up and contain VBA code (.xlam extensions) that add extra functionality. They usually save you time and or help you avoid errors. While there are Add-Ins that are already available, and there are many Add-Ins that are a lot of third-party Add-Ins – a lot of them are free to download. Just take a look around. So, follow the steps below to convert our UDF to re-use.

  • Click on the File Tab
  • Select Info (Excel 2016)
  • Next, click on the Title and Comments – These are the pieces of information that a user sees and uses when they are loading the Add-In.
random string generator
  • Fill in these fields with something appropriate and informative to the user
  • FInally, click Save As and select the file extension .xlam
  • The name you choose here will be the name of the Add-In
  • The file path automatically is changed.You can keep this location or change it
Excel add in generate random string for a password

Install the Add-In To Generate A Random String.

  • Open a new workbook
  • Developer Tab | Add-Ins | Excel Add-Ins
  • Select your Add-In and hit Ok in the dialog box. In this example it is Generate A Rndom String.
  • The Add-In will now be activated.  Your personal random string generator.
Excel add in random string generator in Excel

That’s it. Your very own random string generator. A simple piece of VBA can turn into something really useful. Easy as that. Would you find this useful? Likewise, please share in the comments below and let me know if you have ued the random string generator.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

free Excel tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

How To Excel At Excel – Macro Mondays Blog Posts.

Learn Excel Dashboard Course

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Popular Pages

More Info

Search