 # How to ensure two rows are of equal length

Hi Everyone !

I have a query in excel sheet automation .

I’m having two excel sheets ,

1. is the master excel sheet .this excel sheet will have rows from A to AE .
The column A contains the formulas like (sum=B:AE)
2. This is the another excel file , where I am taking data from this sheet and copying it to
Sheet 1
here I will be copying the values from B columns , since A columns is having the formulas
Now I want to ensure that both the A column length and B to AE column length should match , so that if the A column length is shorter that the column length of Bto AE then the formula should be copied to every cell in the column A .

After you read the master sheet in a datatable (dt) use dt.Rows.Count to get the number of rows. Similarly, read the row count for the other sheet. Then you can compare both the row counts… If the rows count is not equal , can you please let me know how to append the formula in column A after the cell where the formula is not present

There are several ways to achieve this. One way is, after the you get the row count of sheet2, select the last cell of that range in master sheet. Then fill the formula until that cell ctrl+shift+up will select the cells above
ctrl+d will paste the formulas to those selected sheets

You might have to add 1 to the row count depending on the use of headers

@farhan94

Thank you so much for the input .

here I wold like to ask for an alternative to selectors , as those are very unstable .

could you please provide any alternative

You can use a macro. Something like this:

``````Sub MacroSample()
'Lastrow will have the range/row count from B column
Lastrow = Range("B" & Rows.Count).End(xlUp).Row
'We define a formula to be pasted from A2 to the last cell of range
Range("A2:A" & Lastrow).Formula = "=B2+5"
End Sub
``````

You can define your own formula between the double quotes. I used =B2+5 as an example here

2 Likes

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.