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

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.

Answers


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))

Need Your Help

Jquery css z-index Math.random() not working

javascript jquery html css css3

I'm trying to create simple polaroid-style gallery. I'm doing okay with random positioning and css-transition rotation, but when it comes to randoming z-index, i'm failing (i need it, because it's ...

Button automatic HTML

javascript html5

I have a button and this onclick to see a animation on the page that effect is for the id iterrateEffects but I need is effect init automatic (10 sec.), I see on internet is possible with the