In this blog, I am going to share “3 awesome ways to master Excel for beginners”. My previous blog in Tips and Tricks section, I shared macro to unlock password protected excel sheet. It’s time now to share some more. Use it and see your sheet working for you without any flaws.
#1 Merge cells using this simple VB Code
Merging Cells in Excel has no shortcuts. But as it said, this is Excel and you can probably achieve everything here. Go ahead and try this macro to merge your selected cells
Sub MergeCells1()
Selection.Merge
End Sub
Assign the created macro to a keyboard shortcut and get set to go.
But if wish to merge cells and align it to centre, here you go.
Sub MergeCells2()
With Selection
.HorizontalAlignment = xlCenter
.Merge
End With
End Sub
# Delete undesired Web objects from Copy and paste Excel
Copying content from Web and using it in excel comes with a pain in the form of check-boxes, pictures, logos etc. We do not want that, right. Use the below hack to get rid of such obstacles.
• Hit F5 and Excel will display the “Go To” dialog box
• Click “Special” button.
• Select the Objects option
• Click OK.
This will select a number of the objects in the worksheet. Press the Delete key and you are done.
Since I am obsessed with Macro, I would not leave you here. Go ahead and use below macro and be an expert.
Sub DeleteAllShapes()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next
End Sub
# Remove long trailing space in an excel sheet – Space Prefix
We all download data from software’s. Till the time we don’t use any formula and we need number in text format, we are absolutely good to go. Problem is when we try to use formula and we need numbers. Suddenly, we realize there is a trailing space before the number within the same cell. For 10 or 20 cells, we can do it manually. What if data is more than 1000 in number?
No need to say thanks to me. I am here to help. Use the below macro to solve your problem
• Hit Alt+F11
• Go to Insert and Insert Module
• Paste below Macro
Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Trim(c)
Next
End Sub
• Click Save
• Close VB
• Hit Alt+F8
• Click Run
All spaces are removed. Enjoy 🙂
Let me know in comment section if this helped you in solving the problem you might be facing.