Getting a range from the same sheet using the starting cell address and ending cell address

Question

I am pretty new to Excel VBA and I have to create a macro for working on a large amount of data. the data is in the following sample format :

I am writing a function to get the range such that for each cell in column A, such as AAAA, I need to get the range B2:B4. I wrote a function that scans column A till it finds a non empty cell (startCell), then taking reference from that cell, it obtains the first cell B2 by doing :

Worksheets(sheetName).Cells(startCell.Row + 1, 2)

countStart = startCell.Row + 1

Then it runs a loop that counts the no of cells in column B starting at B2 (using countStart as row no.) till it encounters an empty cell and stores the count in say countFinal. I then want to obtain the range between

Worksheets(sheetName).Cells(startCell.Row + 1, 2) 

and

Worksheets(sheetName).Cells(countFinal, 2)

The same needs to be done for the cells with BBBB and CCCC

How can I do this?

PS : I cannot share the code here since the code is on my office machine and I am posting from my home machine. But I can see if I can post it later if needed.

Answer 1

You need to combine starting and ending Range points using this syntax:

Range(StartingCell, EndingCell)

which in your situation could go as follows:

Worksheets(sheetName).Range( _
    Worksheets(sheetName).Cells(startCell.Row + 1, 2), _
    Worksheets(sheetName).Cells(startCell.Row + 1, 2).End(XlDown))