Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

How to put outlines around the cells

Some times we need to put outlines around the cells. Think you have adjacent cells which contains data. So if you put outline to those cells it will clearly separate the data. And it also gives a nice and neat look to your spread sheet. Below code will put outline around each and every cell of range "C3" to "C5".

dim WR as worksheet

set WR=worksheets("Sheet1")

WR.Range(Cells(3, 3), Cells(3, 5)).Borders.LineStyle = xlContinuous

After running above code your spread sheet will look look below.

How to increment days, weeks, months etc.

We often have to deal with dates when we develop programs for real life situations. Because time has vital place in real life. So when we develop programs we often need to add intervals to the dates. We can use "DateAdd" in those cases.

Here below is a explanation of how to use a "DateAdd". We can use this not only to add dates but also to add weeks, months etc. So it will be very helpful when develop programs which are connected with dates.

Result = DateAdd("d", 1, CDate(Range("A1")))

There should be valid date in A1. It will increment that date by a single day.

"d" means we are adding a day.

Below symbols can be used to add other intervals.

s - Second
n - Minute
h - Hour
q - Quarter
m - Month
y - Day of year
yyyy - Year
w - Weekday
ww - Week

This method can be used to substract dates as well. Below will substract 5 days.

Result = DateAdd("d", -5, CDate(Range("A1")))

How to use sum function in VBA

I think you all know about the "Sum" function in excel sheets. If you want to get the sum of values of cells from C5 to C246, you can simply write the formula "=Sum(C5:C246)" at the cell where you want the sum value to be appear.

You can use this function in VBA as well. Then you can get "Sum" value very easily. 

Below is the code for that

Range("C247").Value = Application.Sum(Range("C5:C246"))

Sum value will appear at cell "C247"

Some times there are situations you need to use column numbers instead of column names when developing programs. You can use below code at those circumstances.

Range("C247").Value = Application.Sum(Range(Cells(5, 3), Cells(246, 3)))

please note that above two codes do the same thing. So they gives same values.

Check whether font is bold in a cell

When developing VB programs for excel sheets, some times we need to track certain columns, rows or cells.

For an example think you have to do calculation for each and every cell in a excel sheet, only if there column heading is bold.

So below code can be used to check whether cell "A1" is bold or not


   If DR.Range("A1").Font.Bold = True Then
        Do the calculation
   End If

Best way to get column number of last cell having data

dim WS as worksheet
Set WS=worksheets("sheet1")

'find last column
Lastcol =WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

Number of columns in excel 2010 - An important fact about excel 2010

There are 16384 columns in excel 2010. Column title of last column is "XFD" 

This is how I found it by VBA code.

I wrote below code.

Sub columnLimit()

For i = 1 To 100000
    Cells(12, i).Value = i
Next i

End Sub

And run it in a worksheet. Then I got below error message.

Clicked OK and went to end of columns using scroll bar. Found the result

How to use RGB color system

These two colors give professional look to your excel sheet.

 Range("D2").Interior.color = RGB(245, 245, 245) 'white smoke
 Range("D3").Interior.color = RGB(220, 220, 220) 'gainsboro 

white color can obtained by below RGB value
 Range("D4").Interior.color = RGB(255,255,255) 'white color

 Black color can be obtained by below RGB value
 Range("D4").Interior.color = RGB(0,0,0) 'black color

Find last cell filled with data in a specific column

Think you have data in a cells of column "A". And you want to identify the last cell of that column having data. (i.e. You want to get last row number having data). In such occasion use below code. value of Z-1 will be last row having data. Value of Z will be next empty row.

But there shouldn't be any empty cells in between along that column.


'Find last cell of column "A" filled with data

Dim z As Integer

z = 3
Do Until Worksheets("Sheet1").Range("A" & z).Value = ""
     z = z + 1
Loop '(z-1=Last row having data)

Get a position of a character in a string

There are times we need to get the position of a certain character in a string.Below is a vba code which can be used to get a position of certain character.

Think there is a sentence or a word in a cell "C3".

So below code can be used to get the position of "@" in that sentence or word.


    Dim cellValue As String

    Dim PosOfAt As Integer

    Dim WS1 As Worksheet
    Dim Rng As Range
    Set WS1 = Worksheets("Sheet1")
    Set Rng = WS1.Range("C3")

    cellValue = Rng.Value

    PosOfAt = InStr(1, cellValue, "@", vbTextCompare)

    'Here PosOfAt equals to the position of "@"

Copy and paste a range

Think you have below values in "Sheet1" of your excel workbook. And you want to just copy paste them to similar cells of the "Sheet2". You can do it with one line of code.

Below code will copy values of cells from "A2" to "E2" of sheet1 to same cells of sheet2.


Worksheets("sheet1").Range("A2:E2").Copy Destination:=Worksheets("sheet2").Range("A2")

Best way to get the last row of any column of the excel sheet

We often need to find the last row or last column having data in a excel sheet, when we create VB applications for excel. This last data may be in any row or any column. Some times we need to get the last row or last column of whole work sheet. And some times we need to get the last row of specific column or last column of specific row. So we need to use suitable code according to our requirement. Here below are different codes you can use for different kinds of situations.

If you need to get the last row whole worksheet you can use below code for that kind of scenario.

'find last row
Lastrow = ws1.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Here Lastrow equals to last row number having data. So if you want to enter data to next row you must use Lastrow+1. Last data might be in any column. It gives answer without depending on the column.

Some times you need to get last row number having data in a specific column. For a example if you need to get  last row number having data in column "A" you can use below code.

'column A in this example
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

You need to put above code inside a with block as follows

dim WS as worksheet
set WS=worksheets("sheet1")
With WS
     LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End with

If you need to get  last column having data in row 1 you can use below code.

'row 1 in this example
LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column

Number of rows in Excel 2013

There are various specifications and limits for Excel worksheets and workbooks. Therefore it is very important to know the limits when you develope Excel applications. Because then only you can design the application to fulfil the requirements of the user. For an example, if your application need to process several million rows of data, you can design your application in such a way that user can enter the data in several sheets. If you reserve one sheet for data entry when you design the application, then user may not able to input all the data for processing.

Also the specifications and limits are vary from one excel version to another. However maximum number of rows and columns on a worksheet is same for all the latest versions of Excel applications. So the Excel 2013, Excel 2010 and Excel 2007 can have 1,048,576 total rows and 16,384 total columns in one worksheet. However there is no limit for number of worksheets. It will depend on the RAM limit of your device. But row limit and column limit of Excel 2003 is less than above. Microsoft stop supporting for that version as it is outdated.

So now let’s create simple macros to put number 1 to 1,048,576 in column A. Actually you can do this manually like this. Enter number 1 and 2 to two adjacent cells like this.

Then select those two cells.

Now take your mouse pointer to the bottom right corner of the last selected cell. Then cursor will change to + sign. Then click on the left mouse button and drag it until up to the number you want to fill.

Then release the left mouse button.

So you can use this method to fill numbers easily. But even this method will take considerable time if you want to fill numbers up to more than 100,000. But you can do this much quicker using VBA. Place this following code inside a module in a VBA editor and run it.

Sub FillNumbers()

Dim i As Long

For i = 1 To 1048576
    ActiveSheet.Range("A" & i) = i
Next i

End Sub

It will fill numbers from 1 to 1,048,576 in column A of the active sheet. You can change the for next loop to suit with your requirements. Also you can use similar method to fill a row from 1 to 16384 as well.

Contact Form

Name

Email *

Message *

Popular Posts