Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VBA Excel 2000 - Identify and select Ranges

Posted on 2011-02-24
6
Medium Priority
?
219 Views
Last Modified: 2012-05-11
Dear Experts,

Could you please check the attached small file on Sheet1, it contains just some cells in use, the others are blank.

Do you have idea maybe for a logic, which would check the whole sheet, and as result would identify the first and last cells of such "Blocks".

In the example I would mean on "Blocks" the

1) A1 first cell and last C10
2) E1 to H17
3) J1 to K6

As final target I would like to select the first block of data, around with ActiveSheet.Range(Cells(1, 1), Cells(10, 3)).Select, format and putting totals under of it, after going to the second etc. Just for this would need to know always, that actually which is the last cell of the block, because those can change. I did it manually in the file on Target sheet.

thanks,

thanks,
RangesExample.xls
0
Comment
Question by:csehz
  • 2
  • 2
  • 2
6 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34969784
Dear csehz,

if I understand correct.

You look from  Column A to the first Column with a blank.
There one  Column back you calculate the sum of that column and put it underneath whith total left of that range Total, than you want to mark this range with the borders and the total line coloured yellow.
This should be repeated as asmany groups you can detect.

Question:
is always in row 1 a blank as seperation between groups?

Kind regards

Eric
0
 
LVL 1

Author Comment

by:csehz
ID: 34969845
Hi Eric,

exactly and yes that is sure that always one empty column is between of these groups

thanks,
0
 
LVL 24

Expert Comment

by:Tracy
ID: 34970224
Try this:
Sub CalculateTotals()

    tempcol = 1 'Start at column 1
    lastcol = Range(Cells(1, Columns.Count), Cells(1, Columns.Count)).End(xlToLeft).Column 'Get the very last column with data
    mycounter = WorksheetFunction.CountBlank(Range(Cells(1, 1), Cells(1, lastcol))) + 1 'Count how many blanks there are
    
    For i = 1 To mycounter
        lastcol = Range(Cells(1, tempcol), Cells(1, tempcol)).End(xlToRight).Column
        lastRow = Range(Cells(Rows.Count, lastcol), Cells(Rows.Count, lastcol)).End(xlUp).Row
    
        'Add Total and Value
        Cells(lastRow + 1, tempcol).Value = "Total"
        Cells(lastRow + 1, lastcol).Value = WorksheetFunction.Sum(Range(Cells(1, lastcol), Cells(lastRow, lastcol)))
        
        'Add Highlighting
        Range(Cells(lastRow + 1, tempcol), Cells(lastRow + 1, lastcol)).Interior.ColorIndex = 36
        Range(Cells(lastRow + 1, tempcol), Cells(lastRow + 1, lastcol)).Interior.Pattern = xlSolid
        
        tempcol = lastcol + 2
    Next

End Sub

Open in new window

RangesExample.xls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 24

Accepted Solution

by:
Tracy earned 1000 total points
ID: 34970299
This one is a little better and it adds in the borders too.
RangesExample.xls
0
 
LVL 6

Assisted Solution

by:Eric Zwiekhorst
Eric Zwiekhorst earned 1000 total points
ID: 34970431
dear all,
maybe a little late but here is my version to..
RangesExample-1-.xls
0
 
LVL 1

Author Closing Comment

by:csehz
ID: 34977620
Sorry I had the chance to check only now, both macros are working..

You are fantastic to solve this, I will apply on my live examples. Thanks very much
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question