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.
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"))) |
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.
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 |
dim WS as worksheet
Set WS=worksheets("sheet1")
'find last column
Lastcol =WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
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.
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
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
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) |
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 "@" |
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") |
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
|
|
'row 1 in this example
LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
|
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.