VBA to Select Range to first Column without Header

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.
thutchinsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
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
thutchinsonAuthor Commented:
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
NorieAnalyst Assistant Commented:
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
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

thutchinsonAuthor Commented:
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
thutchinsonAuthor Commented:
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
NorieAnalyst Assistant Commented:
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
krishnakrkcCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thutchinsonAuthor Commented:
Kris, you rock.  This is awesome!  .Resize...that's what I was looking for.  Great code.  Thank you SO much.
0
NorieAnalyst Assistant Commented:
So it was the first row you wanted to give the option to include/not include.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SBS

From novice to tech pro — start learning today.