LinkedIn facebook

  • For a Limited Period!

    Buy 2 ~ Free 1 pax   OR   Get  30% off  for every pax on our scheduled classes

    Analyzing Data with Power BI
    Microsoft Excel Power Query (M365, 2021-2010)
    Microsoft Excel Power Pivot (M365, 2021-2010)

    Please This email address is being protected from spambots. You need JavaScript enabled to view it. for more info.


  • For a Limited Period!

    Buy 2 ~ Free 1 pax   OR   Get  30% off  for every pax on our scheduled classes

    Microsoft Excel - Functions & Formulas (M365, 2021-2003)
    Microsoft Excel - Mastering PivotTable (M365, 2021-2007)
    Microsoft Excel - Interactive Dashboard & Executive Report (M365, 2021-2007)

    Please  This email address is being protected from spambots. You need JavaScript enabled to view it. for more info.

  • Live Interactive Virtual Learning

    Live interactive virtual classes are available for our public scheduled courses as well as group or in-house training requests.

    Experience training in the comfort of your home or office!


  • 10 

    Quality & Satisfaction Guaranteed!

    Our courses are conducted by certified professionals  in their respective fields.

    Participants who require a ‘refresher / repeat’ class of the same title are welcome to join the public classes at Centrilinc again for FREE!

  • Flexible

    Pick 3 courses eligible for the Buy 2 Free 1 package fee from our course list,
    The course with the lowest fee among the 3 will be FREE!

    All 3 participants may attend on the same dates OR choose to attend separately on different dates listed in the public schedule within 12 months from confirmation of registration.

  • Affordable

    The Microsoft Office ‘Essential Series’ courses work out to an average of only SGD46 / MYR133 per day!

VBA - Deleting Empty Rows* 27/10/2001

To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.

Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with 'the value zero
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub

The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".

Microsoft Excel Tips

  • Overcoming common problems - VLOOKUP

    Unable to do a lookup? Entries look the same but lookup returns #N/A error. This is a formatting problem. To overcome this you have a couple of options. One is to copy the data from Excel and paste it to Notepad.

    Continue Reading

  • VBA - Carriage Return* 10/11/2002

    Sometimes you may want to put a line of text on the next row and not let it continue on the first row. See this example in a message box.

    Sub TwoLines()

    Continue Reading

  • Sheets - How to deal with them

    To move a sheet, simply click on the sheet tab & drag to the left or right.
    To rename a sheet, double click on the sheet tab & type in the new name.

    Continue Reading

  • Formatting - Centres Text Across Columns

    When you need to centre text across cell, the most common method used would be the merge & centre tool. However merging cells could cause other problems when selecting a range or when trying to perform a copy and paste.

    Continue Reading

  • Overcoming common problems - Sorting

    Unable to sort your dates correctly? If the dates are entered as a text (eg key in as '1/4/2010) then sorting would give you undesired results. You could use...

    Continue Reading

  • VBA - Going to the 1st Sheet* 27/10/2001

    You can select the first sheet of the workbook without knowing the name of the sheet by referring to it by the index.

    Sub FirstSheet()

    Continue Reading

  • VBA - Duplicates (Highlight duplicates in Bold Red)* 27/10/2001

    There are times you need to highlight duplicate data in your worksheet. This macro does the trick.

    Sub DupsRed()

    Continue Reading

  • Menu Bar

    The menu bars for Office 2000 applications tend to help you save time by displaying the regularly used commands instead of the full list of commands and to display the full list you would have to...

    Continue Reading

  • Formatting - Text Formatting

    There are time you just want to squeeze everything into one cell. Other than adjusting the column width, you could reduce the font size either manually or automatically.

    Continue Reading

  • More Tool bar

    If you're sharing a computer, there's always a smart alec who likes to mess up your toobars. To reset them back to the default style, Right Click any tool bar, Customise, Click the Tool bar tab, select the tool bar you want to reset & click on RESET and presto. It's done.

    Continue Reading

  • VBA - Adding Items to a combobox* 15/03/2002

    To populate a combobox or a listbox is the same. You could add from the code or even from a range of cells in your spreadsheet. To add from the code, just add this line to your code.

    Continue Reading

  • Formatting - Quick Formatting

    It's a rat race out there sometimes. Short cuts do help & is faster then reaching for the mouse. Especially if your mouse is not working properly (pretty common problem for some of us huh?)

    Continue Reading

  • VBA - Functions* 31/01/2002

    Creating function is useful as complicated formulas can be made easier in code than in a spread sheet. Formulas can be protected so that users cannot see or modify them.

    Continue Reading

  • VBA - Current Cell Content* 24/12/2001

    Sometimes we need to know what the cell contains ie dates, text or formulas before taking a course of action. In this example a message box is displayed. Replace this with a macro should you require another course of action.

    Continue Reading

  • Standard toolbar & Formatting toolbar sharing one row

    Again Office 2000 applications tries to save space buy cramping both toolbars into one row. Land is an expensive commodity these days!!! So, just point at the light grey vertical line at the begining of the Formatting toolbar until you see a crossed double arrow head.

    Continue Reading

  • VBA - Close All Files* 23/03/2009

    Sometimes you may want to close all files without saving. Doing it manually is a hassle with the question "Do you wanna save?"

    Sub CloseAll()

    Continue Reading

  • Overcoming common problems - Data Entry

    Entering data in rows & column can be pretty tedious especially when you have wide columns and the great number of columns. Excel has a data form feature that makes data entry a breeze.

    Continue Reading

  • VBA - Goto (a range)* 27/10/2001

    To specify a macro to go to a specific range you can use the Goto method. Here I have already named a range in my worksheet called "Sales". You may also use an alternative method ie the Range select method.

    Continue Reading

  • Columns - How to Adjust Column Width

    This is a simple job. Don't sweat it. When you point between the column headings (That's the Alphabets at the top of the columns A, B, C .... etc) the pointer will turn into a double arrow head.

    Continue Reading

  • VBA - GoTo Sheet* 18/03/2005

    Sometimes we have many sheets or sheets with long names & we cannot view them all. You can select which sheet to go to by using this macro.

    Sub Go2sheet()

    Continue Reading

  • VBA - Copying A Range* 05/01/2002

    Copy data from a specific range can be done with this macro. Here data is copied from the current sheet to the activecell.

    Sub CopyRange()

    Continue Reading

  • VBA - Killing Files* 01/12/2001

    Killing or deleting files is easy. However the files must not be in used.

    Continue Reading

  • Formatting - Clearing Formats

    Sometimes we inherit files belonging to someone else who has a bad taste in colour, fonts & borders. You want to clear all formats quickly before you get sore eyes. What do you do?

    Continue Reading

  • Formulas - More Totals

    Lets say you need to total numbers that are not in a range, example a1, a5, a10. Well normally the formula would be =a1+a5+a10. If you have 10 cells to total up that would be slow coz you need to type in "+".

    Continue Reading

  • VBA - Emailing Workbook* 02/12/2001

    To email your current workbook the following code.

    Sub Email()

    Continue Reading

  • Formatting - Date Formats

    What if you want your dates to show Sunday, 25 December? Simple, select the cell with the date, click Format/Cells/Number/Custom (If you are too lazy just press Ctrl 1 remember?), go to the TYPE text box and...

    Continue Reading

  • VBA - Auto Run* 24/12/2001

    Making your macros run automatically when opening your workbook. You can either use the Auto Open method or the Workbook Open method. These macros will display the message "Hello" when you open the workbook.

    Continue Reading

  • VBA - Current Cell Address* 30/06/2010

    To get the current cell address (or current range) to perhaps incorporate into your formula, you could use the following code.

    Sub MyAddress()

    Continue Reading

  • Rows - How to Adjust Row Height

    Equally simple. Just like adjusting columns, point between the row headings (That's the numbers on the left of the rows) the pointer will turn into a double arrow head.

    Continue Reading

  • VBA - Hiding Sheets* 27/10/2001

    To hide your worksheet from users you can use the following code.

    Sub HideSheet()

    Continue Reading

  • Formulas - Totals

    Excel Tips 1 - Formulas - Total


    Everybody uses “totals”, right? See the example image. Most users would create the total on row 3.

    Continue Reading

  • Formulas - File Name

    It is always a good idea to insert the file name & path and include it in the print range so that you will know the filename & it's location when you need to find the file.

    Continue Reading

  • VBA - Error Trapping* 04/01/2002

    Trapping errors are important as users can do marvelous things to mess up you macros. Here you can use either of these 2 statements.

    - On Error Resume Next OR

    Continue Reading

  • There are lots of short cuts in Excel. Like I always say to my students, when in doubt, Right Click. Whenever you point & right click at an object, a menu associated with the object will pop up.

    Continue Reading

  • VBA - Deleting Empty Rows* 27/10/2001

    To delete empty rows in a selected range we can use the following macro. The macro here uses the For Next Loop. First the macro counts the rows in a selected range to determine the when the macro should stop. The For Next statement acts as a counter.

    Continue Reading

  • VBA - Hiding Excel* 03/09/2002

    You can hide the Excel application with this macro. This disables the user from using the excel menus. Don't forget to set it back to visible.

    Sub HideExcel()

    Continue Reading

  • Row Height related problems (Text Alignment)

    When your row height is much higher the your font, you will notice the contents will be anchored to the bottom of the cell. And that's a bit ugly. What if I want it to be in the centre instead. No problem.

    Continue Reading

  • VBA - Flickering Screen* 27/10/2001

    Sometimes when you run a macro, the screen flickers a lot due to the screen updating itself. This slows the macro done especially when the macro has a lot of work to do. You need to include the statement as shown below.

    Continue Reading

  • Text Boxes (for exact text placement)

    When you need place your text on an exact spot, (for instance when you need to print on pre-printed forms) adjusting the row height & column width can be a big headache coz when you adjust, all other text will move.

    Continue Reading

  • Formatting - Number Formats

    Let's say your numbers need to be 4 digits long. 1 to be shown as 0001, 2 as 0002 and so forth. Click Format/Cells/Number/Custom go to the type text box and type in 0000 & click ok.

    Continue Reading

  • Quick adjustments to a column (Auto Fit)

    If the contents of the cell cannot fit the column & you want it to fit just nicely, you could use auto fit.

    Continue Reading

  • Disappearing Toolbars

    How do you turn on your toolbars? Very simple, point at any tool bar & RIGHT CLICK and select the tool bar that you want to appear. Like I always say, when in doubt...

    Continue Reading

  • Overcoming common problems - Date

    Getting wrong date formats when entering a date? Let's say you entered 1st April 2010 in excel, but upon pressing the Enter key the date shows 4th January 2010 instead.

    Continue Reading

  • VBA - Current Cell Position* 10/03/2002

    Sometimes we need to know the current cell position. This would do the trick.

    Sub MyPosition()

    Continue Reading

  • VBA - Input Box* 27/10/2001

    When you need to get input from users, you can use input boxes. This macro will ask for the user's name and will display a message "Hello" plus the user's name.

    Continue Reading

  • Formatting - Auto Format (more suitable for tables)

    For those who may not have time to format or simple have got an awful sense of colour selection, you could use Excel's Auto Format. First select your range, Click Format / Auto Format and...

    Continue Reading

  • VBA - Joining Text Together* 10/03/2003

    There are times where we import text file into Excel an we get text that are separated. I received an email asking how put these text together. Select across your cells first and run this macro.

    Continue Reading

  • VBA - Current Date* 24/12/2001

    It's a good idea to insert the current date when you save the file so that you can tell if it's the latest version. Of course this is shown under file properties but how many people know where to find it?

    Continue Reading

  • VBA - Counter* 17/02/2002

    To use a counter in your macro, just assign any cell to retain the value. In this example the cell A1 is chosen. Each time the macro is run, it adds the value 1 to the cell A1.

    Continue Reading

  • VBA - Excel Functions* 08/02/2002

    Using Excel functions in VBA is almost the same as using them in a spreadsheet. For example to round an amount to 2 decimal places in a spreadsheet would be;

    Continue Reading

  • VBA - Counting Rows & Columns & Sheets* 27/10/2001

    When you have selected a range, it is sometimes useful to know how many rows or columns you have selected as this information can be used in your macros (for eg when you have reached the end, you will know it is time to stop the macros. This macro will do the trick.

    Continue Reading

  • Formulas - Dates

    Excel recognises dates whether you use slash ("/") or hyphen ("-"). However if you use full stop (".") Excel would assume the date as a text. Therefore you will not be able to customise the date styles or even perform calculations with the date.

    Continue Reading

  • Formulas - More Dates

    Do you want excel to use the system date? Use this formula =today() and presto you can see the system date. Cool huh? But if you just want the system date without using the formula just press ctrl : and Excel will insert the system date.

    Continue Reading

  • VBA - For, Next Loop* 15/01/2002

    A point to note is, try not to use the For, Next loop because this method is very slow...

    Continue Reading

  • VBA - Errors in macros* 01/02/2002

    Ever had a macro running perfectly one day and the next day errors keep on popping up even though you never made changes to that macro? This is no fault of yours.

    Continue Reading

  • Moving Around Excel

    Everybody knows how to ride a mouse (er... move a mouse). Sometimes the keyboard is faster. Well here's a list of controls.

    Page Down = To move 1 page down

    Continue Reading

  • VBA - Active Cell* 05/01/2002

    An active cell is the current cell that is selected. This term is used in many macros. This can be used as a marker.

    Continue Reading

  • Formatting - Borders

    Need to draw tables? If you noticed your formatting tool bar, the border icon has pretty limited border styles. Where the heck do you go to if you want some fancy borders with colours? It's actually under our nose.

    Continue Reading

  • VBA - Inserting Rows* 04/01/2002

    To insert rows required by a user is easy. Here the input box is used so that a user can define the number of rows required.

    Sub InsertRow()

    Continue Reading

  • VBA - Deleting Range Names* 15/03/2002

    To delete all the range names in your workbook, this macro will do the trick.

    Sub DeleteNames()

    Continue Reading

  • Formulas - Text / Strings

    There are times you need include numbers or dates together with your text. Typing it in doesn't do much if the numbers keep on changing because it's a formula and you want it linked to your text.

    Continue Reading

  • Copying & Pasting

    There's more to it than just copy (Edit/Copy or ctrl C) & paste (Edit/Paste or ctrl V). You can copy and control exactly what you want to paste. Example you need to copy a total (where you used a formula) and when you pasted it get an error because of the formula.

    Continue Reading