simple problem

A

anilg0001

Rules Violation
Joined
Jun 7, 2010
Messages
193
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> firstly i am not much familier with english language and excel macros
but i need a simple help

the problem is

i have an excel sheet which contain lot of data at A, B, C, D, E ,F .............

Example

<table class="MsoNormalTable" style="width: 433.5pt; margin-left: 4.65pt; border-collapse: collapse;" width="578" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15.75pt;"> <td style="width: 37.45pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap">
Date​
</td> <td style="width: 37.4pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap"> Sl. Nos.
</td> <td style="width: 37.4pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap"> Company Names
</td> <td style="width: 37.4pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap"> State
</td> <td style="width: 42.75pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="57" nowrap="nowrap">
Zip​
</td> <td style="width: 37.4pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap">
Fields​
</td> <td style="width: 203.7pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="272" nowrap="nowrap"> Remarks
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
########​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> 1
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Abel Lopez
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
9​
</td> <td rowspan="2" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Abel Lopez
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
11​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
​
</td> <td style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap">
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> 2
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Adam
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
2​
</td> <td rowspan="2" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Adam
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> AR
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
72766​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
12​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap">
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> 3
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> CA
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
92843-2300​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
13​
</td> <td rowspan="5" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison, 3rd and 5th contact are not exist
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
3​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
5​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
3​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex CO
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
6​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap">
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> 4
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Alex Beck
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
8​
</td> <td rowspan="3" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Alex Beck
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
8​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Alex Beck
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
8​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap">
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> 5
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Allan
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> FL
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
34446-4271​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
13​
</td> <td rowspan="2" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Allan
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
3​
</td> </tr> </tbody></table>

Some time I want to add new deatails between records. Such case I want to change the sl nos. col B

Is there any macro to put the sl nos auto matically.
(means if there is a blank col then the next column sl no is previous sl no + 1)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Hi there,

The simplest way is this,

Instead of having the actual number in Column B, have a formula - just type in the below in column B:

=row()

This will return the row number, so that if you insert or delete a row, all other rows will adjust automatically.

If you need to offset your rows, (i.e. you have a heading in the first row, and you want Row 2 to display the number 1, you can adapt the formula to be:

=row()-1

and then fill that formula in all cells in Column B.

Hope this helps :)
 
Upvote 0
Sorry
I think it is not possible
because i have 1000 of records in that spreadsheet
some time i need to add NEW records between records in such a case
i want to change the formula again
ex:
if i want to add new record between the sl no 10 and 11
10 anil
11 Ajith

(i am adding kiran as 11) then

10 anil
11 kiran
12 Ajith like this
if i use that formula then i want to change the entire formula after the sl no 11. Got it?

Thanks for ur valuable time. can u solve this?
 
Upvote 0
If I understand correctly, this should work:

Code: 
Sub SL_NOS()
 
Dim FinalRow As Long
FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
Cells(2, 2).Formula = "=Row()-1"
Cells(2, 2).Copy Destination:=Cells(2, 2).Resize(FinalRow, 1)
 
End Sub
 
Upvote 0
anilg0001 said:
Sorry
I think it is not possible
because i have 1000 of records in that spreadsheet
some time i need to add NEW records between records in such a case
i want to change the formula again
ex:
if i want to add new record between the sl no 10 and 11
10 anil
11 Ajith

(i am adding kiran as 11) then

10 anil
11 kiran
12 Ajith like this
if i use that formula then i want to change the entire formula after the sl no 11. Got it?

Thanks for ur valuable time. can u solve this?
Click to expand...


I think this should work.
No need to change the formula for all the the cells in B column.
This formula returns the value of Row Number -1, so you can just copy it anywhere it will return the same value (Row number - 1).
You have to just paste that same formula in blank cell of B column where you have inserted new row for new record.
 
Upvote 0
Hi Anilg0001,

Have you tested my response? As Somnath has suggested, the formula will automatically recalculate based on it's new row number - so the only thing you would have to do when you add a row is to type that formula into Column B ONLY for the new row - as everything else will automatically re-calculate.

Cheers
 
Upvote 0
Thanks mr akbar and somnath
but the problem is not solved
problem number1

i had more than 1000 records
each reacods are not continiously
<table class="MsoNormalTable" style="width: 433.5pt; margin-left: 4.65pt; border-collapse: collapse;" width="578" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15.75pt;"> <td style="width: 37.45pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap">
Date​
</td> <td style="width: 37.4pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap"> Sl. Nos.
</td> <td style="width: 37.4pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap"> Company Names
</td> <td style="width: 37.4pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap"> State
</td> <td style="width: 42.75pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="57" nowrap="nowrap">
Zip​
</td> <td style="width: 37.4pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="50" nowrap="nowrap">
Fields​
</td> <td style="width: 203.7pt; background: none repeat scroll 0% 0% rgb(255, 204, 0); padding: 0in 5.4pt; height: 15.75pt;" valign="bottom" width="272" nowrap="nowrap"> Remarks
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
########​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
1​
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Abel Lopez
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
9​
</td> <td rowspan="2" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Abel Lopez
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
11​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap">
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
2​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Adam
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
2​
</td> <td rowspan="2" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Adam
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> AR
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
72766​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
12​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap">
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
3​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> CA
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
92843-2300​
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
13​
</td> <td rowspan="5" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison, 3rd and 5th contact are not exist
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
3​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
5​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
3​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap"> Alex CO
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
6​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap">
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
4​
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Alex Beck
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
8​
</td> <td rowspan="3" style="width: 203.7pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="272" nowrap="nowrap"> No details for comparison
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Alex Beck
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
8​
</td> </tr> <tr style="height: 12.75pt;"> <td style="width: 37.45pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
</td> <td colspan="2" style="width: 74.8pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="100" nowrap="nowrap"> Alex Beck
</td> <td style="width: 42.75pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="57" nowrap="nowrap">
</td> <td style="width: 37.4pt; padding: 0in 5.4pt; height: 12.75pt;" valign="bottom" width="50" nowrap="nowrap">
8​
</td> </tr> </tbody></table>
Sl no1 contain two group of deatails also sl no 3 contain 5 group of recods

As per ur decision I want to put the equation at b2,b5, b8, b14 etc upto the records number 1000 .am I right?

If I copy the first equation from the b2 that is =ROW()-1 (return 1) it will not work at the b5 col . if I past that equation at b5 then I return 4 insted of 2.
 
Last edited:
Upvote 0
hai mr de909

the macro is not working
it work only at the B2
next i want at b5, b8, b14 etc..........
 
Last edited:
Upvote 0
Try this. This should solve your problem.

Code: 
Sub SL_Nos()

Dim MyRange As Range, Address As String
Dim FinalRow As Long, MyCount As Long

FinalRow = Cells(Rows.Count, 3).End(xlUp).Row

    With Range("C2:C" & FinalRow)
        Set MyRange = .Find(What:="", After:=Range("C2"), LookIn:=xlValues, _
                       LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                       MatchCase:=False)
        If Not MyRange Is Nothing Then
            Address = MyRange.Address
            MyCount = 2
            Do
                MyRange.Offset(1, -1).Value = MyCount
                Set MyRange = .FindNext(MyRange)
                MyCount = MyCount + 1
            Loop While Not MyRange Is Nothing And MyRange.Address <> Address
        End If
    End With


End Sub

This starts numbering every entry after the 1st in the sl.nos column. Make a copy of your file before you run this macro.
 
Upvote 0
Ahh - apologies for that oversight.

DE909's macro does the job - you will just need to rerun when you add / delete data sets, but with that taking just 2 seconds, it should be suitable.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
E
  • Question
VBA for Web Scraping help
Replies
0
Views
591
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
C
  • Question
Formula to find Max number of items in orders
Replies
1
Views
481
fjns
F
A
  • Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A

Forum statistics

Threads
1,214,823
Messages
6,121,777
Members
449,049
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top