Solved

VBA to Select Range to first Column without Header

Posted on 2012-03-17
9
2,959 Views
Last Modified: 2012-03-18
I am using the following code to select a range of cells in a worksheet:
Sub RangeAllSelect()
' Selects the entire populated range of the active worksheet
'
' Keyboard Shortcut: Ctrl+Shift+R
'
LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Range(ActiveCell, Cells(LastRow, LastCol)).Select

End Sub

This code also selects cells outside the continous block of data.  How do I get it to stop selecting columns at the first column that does not have a header?

Thanks for your help.
0
Comment
Question by:thutchinson
  • 4
  • 4
9 Comments
 
LVL 33

Expert Comment

by:Norie
Comment Utility
The code would only select cells in the first column if the active cell was in the first column, whether there was a header or not.

Do you want to change which cell is used for the top-left of the range if the active cell is in the first column?

This will change the top left cell to column 2 if the activecell is in column 1.
LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Range(Cells(ActiveCell.Row, IIf(ActiveCell.Column = 1, 2, ActiveCell.Column)), Cells(LastRow, LastCol)).Select

Open in new window

0
 

Author Comment

by:thutchinson
Comment Utility
I see what you mean. Sometimes we need to copy the headers and sometimes we don't.

The headers will always determine the right side of the range, however.  There is often extraneous and "unwanted" data in columns to the far right of our intended selection.  The unwanted data can't be deleted first because it's performing calculations and such.

Can we specify the row to use as the header row?  Perhaps as user input? After the header row determines the far right column by counting until empty cell, then lastrow to determine the bottom?

As background, we are performing major scrub operations on some nasty sales records that we receive from our clients. That means that the select to copy macro I'm trying to develop needs to be very flexible.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Sorry I'm confused you want to ignore columns on the right not the first column?

Is there any other way you could determine the range of data to delete?

Basing it, partly anyway, on the active cell seems kind of arbritrary.

Could you attach an example workbook?

By the way, in Excel you rarely need to select and you definitely don't need to when copying/deleting/pasting data.
0
 

Author Comment

by:thutchinson
Comment Utility
SampleData-SelectRange.xlsxI am looking for a single macro that is smart enough to find a range of data based on contiguous rows and columns and select it.  I need it to ignore rows below (that may have unwanted or null values) and the unwanted data in columns that are not contiguous.  By "not contiguous" I mean that the headers in Row 1) stop but there is data that I don't want to select in far right cells.

A sample file is attached.  The sample file can be used for both examples.  

Scenerio 1: A want to select from A1 (containing headers in Row 1) to X40. Unwanted data starts in COLS AB.  Note that there are no headers in any column to the right of COL X.  I want the macro to select A1:X40 which includes the headers of those columns.  To be clear, I don't want to select anthing to the right of COL X where the headers stop.

Example 2:  Everything is the same as in example 1.  I want the same data block selected but I don't want the macro to select Row 1 containing the headers.

The code I provided in my question selects all the unwanted data in columns without headers.  
Perhaps Y/N user input "Do you want to select the headers?" is required here.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:thutchinson
Comment Utility
Q: Sorry I'm confused you want to ignore columns on the right not the first column?

Reply: I do not want to ignore the first column.  I want to include the first column.

Is there any other way you could determine the range of data to delete?

Reply: I can't delete the unwanted range because I don't control the source data.  I never know what is going to be out there. Also, sometimes I'm working with a template that has calculation or count data that I put out there to make other macros work.

Basing it, partly anyway, on the active cell seems kind of arbritrary.

REPLY: It works with our workflow.  If I start describing why this is necessary it will make the question more confusing than it has to be.  Trust me on this.

Could you attach an example workbook?

REPLY: Yes.  sample attached to previous post.

By the way, in Excel you rarely need to select and you definitely don't need to when copying/deleting/pasting data.

REPLY:  Sorry, Imnorie. I don't understand this comment.  Of course you have to select data unless you want ALL of it.  I only want SOME of it. You wouldn't believe the crap in the  source files that I get from clients.  There's stuff all over the place.  They don't understand Lists. They don't even give me a unique ID for the record.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
OK, I missed the bit about headers - just picked up the 'first' part.

What if the active cell is in a column/row way outside any of the data?

The last comment I don't understand, if you want to delete/clear/whatever a range in Excel you don't need to select it first.

It's late here, I'll take a look at the file tomorrow.
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 500 total points
Comment Utility
Hi,

May be..

Sub kTest()

    Dim LastRow     As Long
    Dim LastCol     As Long
    Dim Ans
    
    Ans = MsgBox("Do you want to select the headers?", vbYesNo)
    
    With Sheets("Example") '<<< adjust sheet name
        LastRow = .Range("a" & .Rows.Count).End(xlUp).Row
        LastCol = .Range("a1").CurrentRegion.Columns.Count
        
        If Ans = vbYes Then
            'with headers
            .Range("a1").Resize(LastRow, LastCol).Select
        Else
            'without headers
            .Range("a2").Resize(LastRow - 1, LastCol).Select
        End If
        
    End With
    
End Sub

Open in new window


Kris
0
 

Author Closing Comment

by:thutchinson
Comment Utility
Kris, you rock.  This is awesome!  .Resize...that's what I was looking for.  Great code.  Thank you SO much.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
So it was the first row you wanted to give the option to include/not include.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
If you are a user of the discontinued Microsoft Office Accounting 2008 (MSOA) and have to move to a new computer running Windows 8, you will be unhappy to discover that it won't install.  In particular, Microsoft SQL Server 2005 Express Edition (SSE…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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

11 Experts available now in Live!

Get 1:1 Help Now