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 .

can anyone please help me with this

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…

image

Hi @farhan94

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

image

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.