Selecting a dynamic range using code

[Glaswegian]

Think I'm having a very bad day....

I'm using Chip Pearson's code to import some text files - no problems. I then do some formatting, sorting and so on - again no problems.

However, there is one file (56,000 rows) where I'm having a problem. I'm using some code to insert one blank line where a cell value in one column changes. This column has a Manager ID code. I then need to copy the area above the blank line and paste to a new sheet. There are also 2 blank columns in the middle (used by Managers for input) and this is causing my problem (I think!)

I can't seem to select the correct range. It should be from A6 to J (the row above the blank line). The row will differ from file to file ie A6:J12000 or next time A6:J500 and so on. I've tried variations on the following

ActiveSheet.Range("A6", Range("J6", Range("J65536").End(xlUp))).Select

but can't quite seem to get it right.

Many thanks for any assistance.

[johnske]

?Your zip file has no code in it?...

If you mean to select all down to J65536 this will do it,

Sub DoIt1()
ActiveSheet.Range("A6:J65536").Select
End Sub

If you mean to just select all down to (some range that has yet to be found) maybe this will do it,

Sub DoIt2()
Dim Val1 As Range, MyChoice As String
'(write your own code to find MyChoice...Example is an Input box)
MyChoice = InputBox("Insert your range here")
'(end of your code)
Set Val1 = Range(MyChoice)
ActiveSheet.Range("A6", Val1).Select
End Sub

]

[Jacob Hilderbrand]

Try this:

Dim LastRow         As Long
LastRow = Range("J65536").End(xlUp).Row
    Range("A6:J" & LastRow).Select

[Glaswegian]

Thanks to both for your replies.

"your zip file has no code in it.." - see what I mean?

The code (included this time!) inserts a blank row and then tries to copy the range above the blank row to another sheet. It's the range above the blank row that I can't seem to work out - I need to include cells from A to J.

johnske - Input Box is not really an option.
Jacob - your code didn't work - like me efforts, it's still only selecting the first 6 lines.

Thanks and regards

[Richie(UK)]

Hi Iain,

A number of observations, if I may.

1. Unqualified references can lead to unexpected results. It would be more reliable if you declared a worksheet variable and then referred to it. Like this:

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sample")
lastRow = ws.Range("J65536").End(xlUp).Row

2. In the example worksheet column J contains no data so lastrow will always be = 1. This explains why the first 6 lines are being selected - effectively A6:J1.

3. The End(xlUp) approach is ideal for establishing the last row. However, you are not looking for the last row you are looking for a break in contiguous data caused by your insertion of a blank line. You could use A6 as the starting point and then use End(xlDown) to establish a reference. However, have you considered applying an AutoFilter based upon your chosen criteria and then copying the visible cells? This is likely to be far less troublesome and easier to adapt when copying subsequent blocks of data.

HTH

[Glaswegian]

Richie



Yes, I've just realised that I was using the wrong column!

I'm now part of the way there, using this

ActiveSheet.Range(Cells(5, 1), Cells(lastRow, 10)).Select

but still no luck. I'll have a look at the visible cells.

Thanks again.

[Glaswegian]

Richie

Thanks for your comments and suggestions. I've solved it by using the Autofilter and cutting the filtered selection.

Guess I just needed a nudge in the right direction!

Thanks again for your help.

Regards