Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Simple macro to find the next blank column in the range

0

Hi Guys,

I have data for same month in multiple columns in a continuous range.

Each month range is separated by a blank column.

What I want is a macro that find the next blank cell in column starting cell B10 and autosum the columns preceding the blank cell.

I want autosum in highlighted columns in yellow (must be a loop).



I started writing the code but stuck:

Sub sum()
ActiveSheet.Range("b10").Select
Selection.Columns.End(xlToRight).Offset(0, 1).Select
ActiveCell.Value = "=Sum(columns.end (xltoleft)"



End Sub


Many thanks 
Sophia
excelmacros-vba
MS Office Version: 2013
posted by: ferocious12 (rep: 3) on Thu Sep 29, 2016 at 11:22 pm
Answer
Discuss

Answers

0

Try this macro:

Sub sum_macro()

last_row = Range("A" & Rows.Count).End(xlUp).Row


For cur_row = 10 To last_row

    last_col = Cells(cur_row, Columns.Count).End(xlToLeft).Column

    Cells(cur_row, last_col + 1).Formula = "=SUM(" & Range(Cells(cur_row, 1), Cells(cur_row, last_col)).Address(False, False) & ")"

Next cur_row

End Sub

You had a few pieces here and there, but keep practicing and you will understand macros in no time!

posted by: don (rep: 1989) on Fri Sep 30, 2016 at 9:58 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login