PDA

View Full Version : Solved: Finding start / end of data within a range - with a twist



lostin_space
08-23-2006, 08:44 AM
hi all, i've got a worksheet with titles & a logo at the top of it.

I need to ignore all this, and then find the 'start row' of my 'actual data', and then establish where the end of my 'real' data is. i.e. ignore B1:AR13, and identify the data within B14:AR71, and then copy that chunk of data over to a new sheet...

I've had a look at http://vbaexpress.com/kb/getarticle.php?kb_id=496 + a couple of others, bust just can't seem to make any of them work...

anyone got any ideas, on how to ignore a load of rows, then identify a chunk of data after that & copy it over to a new sheet?

Cheers

Russ

:dunno

Bob Phillips
08-23-2006, 09:25 AM
You need to have some rule to identify the data row, so we can't answert that bit without that info.

The last bit would just be



LastRow = Range("B14").End(xlDown).Row

where you would replace B14 with wherever you found your start row.

lostin_space
08-23-2006, 09:37 AM
sorry - not following you.... my 'actual' data, starts at row 14.... cell B14, and goes across to AR14

mdmackillop
08-23-2006, 09:42 AM
There are different ways to approach this, depending upon the formulae etc. contained in your range and your eventual purpose. If you want to post a sanitised version of your workbook, perhaps we can assist further.

johnske
08-23-2006, 09:07 PM
Option Explicit
'
'set your own source and destination sheets
'
Sub CopyAndPasteEverything()
Sheets("Sheet1").Range("B14:AR71").Copy _
Destination:=Sheets("Sheet2").Range("A1")
End Sub
'
Sub CopyToLastRowAndPaste()
'
Dim Cell As Range
'
Set Cell = Sheets("Sheet1").Columns("B:R") _
.Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious)
If Not Cell Is Nothing Then
If Cell.Row > 13 Then Sheets("Sheet1").Range("B14:AR" & Cell.Row).Copy _
Destination:=Sheets("Sheet2").Range("A1")
End If
'
End Sub

lostin_space
08-24-2006, 02:01 AM
example data attached.

I tried the example code above. It *did* work, but did'nt copy any of my shapes (diamonds, used to indicate project gates / milestones), nor any of the formatting (please see example file for details) which is critically important that it is kept.

TIA

Russ

johnske
08-24-2006, 04:50 AM
Option Explicit
'
Sub CopyToLastRowAndPaste()
'
Dim Cell As Range, N As Long
'
Application.ScreenUpdating = False
Set Cell = Sheets("EBU-AA").[B:AR].Find("*", LookIn:=xlValues, SearchDirection:=xlPrevious)
With Sheets("Sheet2") '<put your destination sheet here
If Not Cell Is Nothing Then
If Cell.Row > 10 Then Sheets("EBU-AA").Range("B11:AR" & Cell.Row).Copy .[B1]
For N = 1 To 45
.Columns(N).ColumnWidth = Sheets("EBU-AA").Columns(N).ColumnWidth
Next
.Rows("1:" & Cell.Row - 10).RowHeight = Sheets("EBU-AA").[11:11].RowHeight
.Activate
With ActiveWindow
.Zoom = 75
.DisplayGridlines = False
End With
End If
End With
Application.ScreenUpdating = True
'
End Sub

lostin_space
08-24-2006, 05:32 AM
perfect! worked like a treat!

Thanks again!

:thumb :bow: :clap: