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?
 
krishnakrkcConnect With a Mentor Commented:
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
 
NorieVBA ExpertCommented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
NorieVBA ExpertCommented:
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
 
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
 
NorieVBA ExpertCommented:
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
 
thutchinsonAuthor Commented:
Kris, you rock.  This is awesome!  .Resize...that's what I was looking for.  Great code.  Thank you SO much.
0
 
NorieVBA ExpertCommented:
So it was the first row you wanted to give the option to include/not include.
0
All Courses

From novice to tech pro — start learning today.