Select range instead of specific cell value with VBA

Question

I got little project in VBA and stuck on below topic

I need to Sum selected range in first empty cell in B column. I tried a small macro in which it sums the same row which mentioned in the vba.

This is what I've found and try to use

Sub Macro9()
   '
   ' Macro9 Macro
   '
   '
    Range("A3").Select
    Selection.Copy
    Range("A2").Select
    ActiveSheet.Paste
    Range("B2").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+SUM(R[1]C:R[3]C)"
    Range("B2").Select
    Selection.Copy
    Range("B3").Select
    Selection.End(xlToRight).Select
    Range("S2").Select
    Range(Selection, Selection.End(xlToLeft)).Select
    ActiveSheet.Paste
    Selection.End(xlToLeft).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Final.xlsx").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False
    Range("A6").Select
    Windows("copy.xlsm").Activate
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Selection.End(xlToLeft).Select
End Sub

I tried to searched for last non-empty cell in selected range so it won't search the whole column

Hello, first of all try to avoid .Select https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 — Teamothy, Sep 25 '19 at 05:24
Hi Teamothy, Kindly suggest me with some changes in my code. I am a beginner. — Roguen, Sep 25 '19 at 05:38
Hello Roguen and welcome to SO. I'm sorry, but your question is not very clear. Can you edit it so we understand what you try to achieve ? eg. Are you trying to select each non-empty cell in B:B and sum it in C1 ? Are you familiar with defining variables at the beginning of your macro ? — JLCH, Sep 25 '19 at 05:48
Hi I have added a snapshot. In this case i would like to have the sum in B2 cell (I.e) B3 to B6 cells are summed in B2. If i change the number of rows present in the column B the code takes only the B3 to B6 but i need to sum from B3 to Non empty cell which may be B7 — Roguen, Sep 25 '19 at 06:01

Dhirendra Kumar · Accepted Answer · 2019-09-25T06:38:09.527

0

To get the sum of all the non empty cells in Column "B" and return it to Column "B2" as I can understand with the image, following is the code which help you:

Range("B2").FormulaR1C1 = "=Sum(R3C:R" & ActiveSheet.UsedRange.Rows.Count + 1 & "C)"

You may use following code to get the sum of till first empty cell:

Range("B2").FormulaR1C1 = "=Sum(R3C:R" & Range("B3").End(xlDown).Row & "C)"

edited Sep 25 '19 at 06:38

answered Sep 25 '19 at 06:13

Dhirendra Kumar

333
2
5

Hi It is working but in my case i would like to sum only the first set of non empty cells and return the value in B2. After getting i will take the value to new sheet. Then i will replace the old values with different row number. – Roguen Sep 25 '19 at 06:26

score 0 · Answer 2 · answered Sep 25 '19 at 06:34

use SpecialCells method of Range object to get not empty cells in a given range

then use Areas property of Range object to loop through each group of consecutive not empty cells

Option Explicit

Sub SumThem()
    Dim c As Range

    For Each c In Intersect(ActiveSheet.UsedRange, Columns(2)).Offset(1).SpecialCells(xlCellTypeConstants).Areas ' loop through each "area" (i.e.: group of consecutive cells with "constant" values) of column B from row 2 downwards
        c.Resize(1).Offset(-1, 0).Formula = "=+SUM(" & c.Address & ")" ' place the sum of current "area" in the cell right above it
    Next
End Sub

Great. In my case Column will Have a set of unique values. I m calculating the set of non empty values and pasting it in the B2. Then i will take down the values corresponding to A2 and B2 to new sheet. Then i will replace the old value with new set of values i.e i will delete the entire column which i have summed. the proceeding value will come to B3 correspondingly — Roguen, Sep 25 '19 at 07:04

Select range instead of specific cell value with VBA

2 Answers2