Excel
Get flash to fully experience Pearltrees
Converting Excel column number into column name, for example 1 into A or 52 into AZ, is needed occasionally, and the following function will do the convertion for us. This entry was posted in Workbook and Worksheet and tagged column name excel , column number into column name , excel column name , excel macro column name , excel macro column number , excel vba column name , excel vba column number , excel vba columns , excel vba get column name , how to get column name in excel , VBA column name , vba excel column name . Bookmark the permalink .
Convert Number to Excel Column Name
Now that Excel has Conditional Formatting (since Excel 97) we can use it to create an alternate row color for a table of data. This is often referred to as color banding and means that every second row should be filled with a specified color. How to: Alternate Row Colors/Color Banding Let's say you have a table of data Starting in A1 and ending in D6.
Alternate background colors
Got any Excel Questions? Free Excel Help Would you like to give your Excel Workbook a ' polished finish ' and give your project a professional feel. We can do this by creating a Splash Screen that shows upon the Workbook opening for about 5:10 seconds, then closes itself automatically. This is amazingly simple to do, even if you have have no VBA knowledge at all! Just follow the steps below;
Splashscreen
Toolbars
Au lieu d'insérer des boutons de commande directement sur la feuille Excel, nous allons voir dans cet article comment créer une barre de menus et y insérer des contrôles. La première étape consiste à créer et ajouter à l'application, dans notre cas Excel, une nouvelle barre de menus qui comportera les contrôles personnalisés. Sub Ajout_BarreMenu () Dim Cbar As CommandBar Set Cbar = CommandBars . Add (Name, [Position], [MenuBar], [Temporary]) Cbar . Propriété = ValeurPropriété End Sub
:= False , Transpose:= False Simple isn’t it? Simple code, but will help us a lot each time we wanna move the active cell into one cell below the last cell contained any kind of data, in the same column. One requirement needed to use this simple excel vba macro , first we need to select the first cell in the same column where we wanna do the selection. This entry was posted in Excel VBA Function and tagged ActiveCell , excel macro move cursor , excel macro select cell below , excel vba , excel vba find last row , excel vba find last row with data , excel vba move cursor , last row with data , move cursor , Offset , vba macro , vba move cursor . Bookmark the permalink .
Move Cursor To One Cell Below Last Row With Data | Excel VBA Macro Tutorial and Examples
VBA Delete Excel Rows Based on Certain Date | Excel VBA Macro Tutorial and Examples
Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date. The algorithm is like this: I’ll create a loop from the first row until the last row with data and check if the date is before Jan 1st, 2009 or not, if it is, simply delete that row.With the simple Excel macro below, we can make all cells in active Worksheet that contains Error value in it, like #NULL, #Div/0!, #VALUE!, #Ref, #NAME?, #NUM!, And #N/A, will automatically having cell format that different/stand out among all other cells. In this example, background color of the cell that contains the error value will automatically change color to red, each time the Excel Worksheet activated.
Auto Format Excel Cells with Error Value | Excel VBA Macro Tutorial and Examples
Protect/Unprotect
Tonight I want to automatically protect and unprotect all Microsoft Excel Worksheets in my Workbook with password, instead of doing it manually, I put these following Excel VBA macro code in my Workbook. If objSheet.ProtectContents = True Then objSheet.Unprotect "a@#&ladfl&^" We only need to call the procedure ProtectAllSheets to protect with password all Excel Worksheets in our Workbook, and calling UnProtectAllSheets will reversed the effect.
Schedule a macro
I once asked by Joshuacht about how to make a macro running every 15 minutes. Let say because every 15 minutes we have to refresh or reload the data in the Workbook. Below is a sample of how we can do this in Microsoft Excel *just an outline*. The logic is simple, when we open the Workbook, we call for the first time the procedure needed to refresh the data. In that procedure, we also add a code to call back the procedure itself 15 minutes later. So while the Workbook is still open, the procedure will continue to call itself every 15 minutes.