Selecting active cell row and rows below.

[datafiend]

Hi there
I've got some code that locates a cell in column D based on its color. I then want to copy that entire row and all the rows below it to another worksheet. I can only find code that copies to the left or right of the active cell, but not the whole row. There are also some empty rows in the range to be copied.

Thanks for any help anyone is able to provide.

datafiend

[Tinbendr]

I've got some code that locates a cell in column D based on its color.

Using that code, determine the row number of the found color.

Calculate the last row of the sheet, then combine the two values to define the range required to copy.

[datafiend]

Thanks for your answer. Unfortunately, my Excel skills are not good enough to understnd it properly. Below is the code I am using which almost does what I want. I copied and modified the code to select the colored range and have added the last line to try and then select the data in the row with the first cell selected and the data below it but can't make it work.

Please Login or Register  to view this content.

Private Sub CommandButton2_Click()
'Standard module code, like: Module1.
Dim rngMyRange As Range, rngMyFound As Range
Dim lngMyRows&, lngMyCols&, r&, c&
Dim strMyAddr$

Set rngMyRange = ActiveSheet.UsedRange

lngMyRows = rngMyRange.Rows.Count
lngMyCols = rngMyRange.Columns.Count

On Error Resume Next

For r = 1 To lngMyRows
For c = 1 To lngMyCols

'If ActiveSheet.Cells(r, c).Value <> "" Then
If ActiveSheet.Cells(r, c).Interior.ColorIndex = 6 Then
strMyAddr = strMyAddr & ActiveSheet.Cells(r, c).Address(False, False) & ", "
End If
Next c
Next r

'This is the found address string!
strMyAddr = Left(strMyAddr, Len(strMyAddr) - 2)

'This is the found Range Object!
Set rngMyFound = ActiveSheet.Range(strMyAddr)

'Do something with the found range!
ActiveSheet.Range(strMyAddr).Select

'Column D is the first column with a yellow coloured cell. If I use A10478576 in the line
'below it selects the data from columns A to D; if I use J1048576, selects columns D: J. How can I
'select columns A:J?
Range(ActiveCell, Range("A1048576").End(xlUp)).Select

End Sub

Please Login or Register  to view this content.

I can't work out what I need to put to select all the data.

Thanks for your help

datafiend

[Tinbendr]

Please use Code Tags when posting code.

OK, I think I know what you want, but how are you determining how many columns to copy? Or is it always A-J from the first Yellow Cell in Column D to the end of data?

[datafiend]

Yes, that's right. Always columns A to J from the first yellow cell in D.


Thanks

datafiend

[Tinbendr]

Please Login or Register  to view this content.

[datafiend]

Thanks, Tinbendr. That works perfectly...and so much simpler than all the code I used for selecting the yellow colored cell. I'll try and learn something from it though counting and For...Next usually confuses me.

datafiend