Solved

VBA Excel 2000 - Identify and select Ranges

Posted on 2011-02-24
6
211 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:broomee9
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 24

Accepted Solution

by:
broomee9 earned 250 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 250 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now