Macro for Couning on Blank columns

absamant

New Member
Joined
Oct 17, 2006
Messages
11
I recieve numerous data sheets where the number of columns the data runs into varies. For Eg City A Data runs till column X and city b data runs into column AA.

As part of my analysis i require to check the diffrences in my last three columns
eg: x2-w2, y2-x2 and z2-y2. However I cannot recored a comon macro for this as the no of columns keeps changing with every sheet.

Would love if someone would help out with a macro which would calculte the last column till which data is populated and
then paste the formula to calculate the difference in the last three columns

Thanks
Ameya
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
Sub Test()
    Dim Sh As Worksheet
    Dim LastCol As Integer
    Dim Lastrow As Integer
    Set Sh = ActiveSheet
    With Sh
        LastCol = Cells(1, .Columns.Count).End(xlToLeft).Column
        Lastrow = Cells(.Rows.Count, 1).End(xlUp).Row
        .Range(.Cells(2, LastCol + 1), .Cells(Lastrow, LastCol + 3)).FormulaR1C1 = "=RC[-3]-RC[-4]"
    End With
End Sub
 

absamant

New Member
Joined
Oct 17, 2006
Messages
11
hi, thanks for the quick response, however, the problem remains, can you tell me a way to post screen shots so that the problem can be explained better
 

absamant

New Member
Joined
Oct 17, 2006
Messages
11

ADVERTISEMENT

tried the code and it worked fanastic,
however there were two issues, my data starts from row 14 onwards in all sheets, can i instruct the macro to execut only from row 14 onwards,
also, is there a wy we can insert an empty column between the last data coloumn and the first column calculatin the difference

thanks
ameya
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Untested, but try:

Code:
Sub Test() 
    Dim Sh As Worksheet 
    Dim LastCol As Integer 
    Dim Lastrow As Integer 
    Set Sh = ActiveSheet 
    With Sh 
        LastCol = Cells(1, .Columns.Count).End(xlToLeft).Column 
        Lastrow = Cells(.Rows.Count, 1).End(xlUp).Row 
        .Range(.Cells(14, LastCol + 1), .Cells(Lastrow, LastCol + 3)).FormulaR1C1 = "=RC[-3]-RC[-4]" 
        .Cells(1, LastCol + 1).EntireColumn.Insert
    End With 
End Sub
 

absamant

New Member
Joined
Oct 17, 2006
Messages
11

ADVERTISEMENT

the revised macro has given an error,
instead of adding a column after the last data column and then calculating the diffrence , it is adding a column in the second column( column B) itself and the difference is being calculated in columns C,D,E,

can u please help on this sticky issue
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What have you got in row 1 on your worksheet? LastCol is the number of the column containing the last entry. For the code to work row 1 has to contain an entry in the last column containing data.
 

absamant

New Member
Joined
Oct 17, 2006
Messages
11
the first 13 rows contain some headings like brand , city etc which are essential to the report, the actual data begins row 14 onwards ( as per YOURr instructions i chcked the data, the last column containing data begins from row 14)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Say your last entry in row 14 is in column Z. For my code to work the last entry in row 1 must also be in column Z. Otherwise change:

LastCol = Cells(1, .Columns.Count).End(xlToLeft).Column

to:

LastCol = Cells(14, .Columns.Count).End(xlToLeft).Column
 

Forum statistics

Threads
1,141,758
Messages
5,708,358
Members
421,566
Latest member
7Nabisco

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
Top