Solved

VBA to Select Range to first Column without Header

Posted on 2012-03-17
9
3,364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 37733116
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
ID: 37733186
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 34

Expert Comment

by:Norie
ID: 37733345
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thutchinson
ID: 37733633
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
 

Author Comment

by:thutchinson
ID: 37733711
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 34

Expert Comment

by:Norie
ID: 37733807
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
ID: 37734102
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
ID: 37734751
Kris, you rock.  This is awesome!  .Resize...that's what I was looking for.  Great code.  Thank you SO much.
0
 
LVL 34

Expert Comment

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

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

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 a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

738 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