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)
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.
You need to combine starting and ending Range points using this syntax:
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))