In this example I am writing the data to first Cell of the Worksheet. Sub sbWriteIntoCellData() Cells(1, 1)="Hello World" 'Here the first value is Row alue and the se!ond one is !olun value 'Cells(1, 1) eans first row first !olun #nd Sub
In this example I am reading the data from Range B3 and Wr Writing iting the data into C5 using Cell Objet. Sub sbReadWriteCell#$a%le1() '&sin Cell be!t Cells(*, +) = Cells(+, ) -s.o$ Cells(*, +) #nd Sub
In this example I am reading the data from first Cell of the worksheet. Sub sb/etCellData() -s.o$ Cells(1, 1) 'Here the first value is Row alue and the se!ond one is !olun value 'Cells(1, 1) eans first row first !olun #nd Sub
In this example I am seleting a Range Rang e using !elet method of Range. Sub sbSele!t0Rane() 'ou !an also use Rane be!t Rane("C+")2Sele!t 'Colle!tion of Cells R -ulti%le Cells = Rane Rane (".3C4")2Sele!t ' It will Sele!t .,.+,.4,C,C+,C4 #nd Sub
'In this e$a%le I a Co%5in the Data fro Rane ("013.16") to Rane(#1") Sub sbCo%5Rane() '-ethod 1 Rane("013.16")2Co%5 Destination3=Rane("#1") 'Here the first %art is sour!e rane, 'and the se!ond %art is taret rane or destination2
'7aret !an be either one !ell or sae si8e of the sour!e rane2 '-ethod Rane("013.16")2Co%5 Rane("#1")2Sele!t 0!tiveSheet29aste 'In the se!ond ethod, first we !o%5 the sour!e data rane '7hen we sele!t the destination rane '7aret !an be either one !ell or sae si8e of the sour!e rane2 '7hen we %aste in the a!tive sheet, so that it will %aste fro a!tive rane 0%%li!ation2CutCo%5-ode = :alse #nd Sub
Instructions:
". Open an exel workbook #. $nter some data in !heet" at %"&B"' 3. (ress %lt)*"" to open +B% $ditor ,. Insert a -odule for Insert -enu 5. Cop the abo/e ode and (aste in the ode window 0. !a/e the file as maro enabled workbook 1. (ress *5 to run it 2ow ou should see the reuired data 4from Range %" to B"' is opied to the target range 4Range $" to *"'. Sub sbClearCells() Rane("013C16")2Clear #nd Sub Clearing Only Data of a Range using ClearContents Method
6his method will lear onl lear the ontent or data of the range not formats 4*ormats remain same Sub sbClearCellsnl5Data() Rane("013C16")2ClearContents #nd Sub Clearing Entire Worksheet using Clear Method
6his method will lear entire worksheet inluding formats. Sub sbClear#ntireSheet() Sheets("Sheet;ae")2Cells2Clear #nd Sub Clearing Only Data from Worksheet using ClearContents Method
6his method will lear onl data of worksheet7 not formats. Sub sbClear#ntireSheetnl5Data() Sheets("Sheet;ae")2Cells2ClearContents #nd Sub Instructions:
". Open an exel workbook #. $nter some data in !heet" at %"&C"' 3. (ress %lt)*"" to open +B% $ditor ,. Insert a -odule for Insert -enu 5. Cop the abo/e ode and (aste in the ode window 0. !a/e the file as maro enabled workbook 1. (ress *5 to run it 'In this #$a%le I a !hanin the Rane .4 :ont Color Sub sbChane:ontColor() '&sin Cell be!t Cells(4, )2:ont2ColorInde$ = + ' + indi!ates Red Color '&sin Rane be!t Rane(".4")2:ont2ColorInde$ = + '<<< ou !an use use R/., instead of ColorInde$ << '&sin Cell be!t Cells(4, )2:ont2Color = R/.(**, 6, 6) '&sin Rane be!t Rane(".4")2:ont2Color = R/.(**, 6, 6) #nd Sub Instructions:
". Open an exel workbook
#. $nter some data in Ranges mentioned abo/e 3. (ress %lt)*"" to open +B% $ditor ,. Insert a -odule for Insert -enu 5. Cop the abo/e ode and (aste in the ode window 0. !a/e the file as maro enabled workbook 1. (ress *5 to exeute itit 8. 6he following examples will show ou how to hange the font to bold in $xel using +B%. >2 'In this #$a%le I a !hanin the Rane .4 :ont to .old 162 Sub sbChane:ont7o.old() 112 12 '&sin Cell be!t 1+2 Cells(4, )2:ont2.old = 7rue 142 1*2 '&sin Rane be!t 1?2 Rane(".4")2:ont2.old = 7rue 1@2 1A2 #nd Sub
*ollowing are the examples to show ou how to do this pratiall. Sub sbChaneC0S#() '&%%er Case Rane("0+") = &Case(Rane("0+")) 'Bower Case Rane("04") = BCase(Rane("04")) #nd Sub Instructions:
*ollow the below instrutions to do it ourself. ". Open an exel workbook #. %dd some text at %3 and %, ranges 3. (ress %lt)*"" to open +B% $ditor ,. Insert a -odule for Insert -enu 5. Cop the abo/e ode and (aste in the ode window
0. !a/e the file as maro enabled workbook 1. (ress *5 to exeute it n this $xample below I am hanging the Range B3 Bakground Color using Cell Objet Sub sbRane:illColor#$a%le1() '&sin Cell be!t Cells(+, )2Interior2ColorInde$ = * ' * indi!ates .lue Color #nd Sub Example 2
In this $xample below I am hanging the Range B3 Bakground Color using Range Objet Sub sbRane:illColor#$a%le() '&sin Rane be!t Rane(".+")2Interior2ColorInde$ = * #nd Sub Example
We an also use R9B olor format7 instead of Co lorIndex. !ee the following example& Sub sbRane:illColor#$a%le+() '&sin Cell be!t Cells(+, )2Interior2Color = R/.(6, 6, *6) '&sin Rane be!t Rane(".+")2Interior2Color = R/.(6, 6, *6) #nd Sub Example !
6he following example will appl all the olorIndex form " to 55 in %ti/esheet. Sub sb9rintColorInde$Colors() Di iCntr :or iCntr = 1 7o *? Cells(iCntr, 1)2Interior2ColorInde$ = iCntr Cells(iCntr, 1) = iCntr ;e$t iCntr #nd Sub Instructions:
". Open an exel workbook #. (ress %lt)*"" to open +B% $ditor 3. Insert a new module from Insert menu ,. Cop the abo/e ode and (aste in the ode window 5. !a/e the file as maro enabled workbook 0. (ress *5 to exeute the proedure 1. :ou an see the interior olors are hanging as per our ode
*ollowing is the example to show ou how to merge or ;n-erge the ells in exel using +B%. Code to merge Cells
Sub sb-ereRane() Rane("013.+")2-ere #nd Sub Code to unmerge Cells
Sub sb&n-ereRane() Rane("013.+")2&n-ere #nd Sub Instructions:
". Open an exel workbook #. (ress %lt)*"" to open +B% $ditor 3. Insert a -odule for Insert -enu ,. Cop the abo/e ode and (aste in the ode window 5. !a/e the file as maro enabled workbook 0. (ress *5 to exeute it 6he following ode will show ou how to add and lear omments using $xel +B%.
Code:
Sub sb0ddCoent() 'Deletes #$istin Coents Rane("0+")2ClearCoents
'Creates Coent Rane("0+")20ddCoent Rane("0+")2Coent27e$t 7e$t3="7his is #$a%le Coent 7e$t"
#nd Sub
Instructions:
". Open an exel workbook #. (ress %lt)*"" to open +B% $ditor 3. Insert a -odule for Insert -enu ,. Cop the abo/e ode and (aste in the ode window 5. !a/e the file as maro enabled workbook 0. (ress *5 to exeute it
