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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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