How do I select a row using a range variable?

[evild]

I have the following code:

I have the question at the end of code. It is well commented so should be eay to follow.

Dim cellrng As Range
For Each cellrng In Range("A:A")
  checkdata = cellrng.Value
  If Not IsEmpty(checkdata) Then
    'Check that the data is not already in sheet1
    If presence(checkdata, sheet1, sheet2b) = False Then
       'Copy Row from sheet1 to sheet2b with defaults
       Call AddData(cellrng, sheet1, sheet2b)
       MsgBox ("New Data Added!")
    End If
  End If
Next cellrng

...
...

    Public Sub AddData(cellrange As Range, sprdsheet As String, sprdsheet2 As String)
    ' Copies an entire row in spredsheet2 and pastes it into the next available row in sprdsheet
         
         'Move to sheet where data resides
         Windows(sprdsheet2).Activate
         'Select current row and copy
         cellrange.Select
         Selection.Copy
         'Move to sheet where row is to be copied to
         Windows(sprdsheet).Activate
         'Move to the next available empty row
         Range("A1").Activate
         'Going to the last populated row
         Selection.End(xlDown).Select
         'and then down one
         Range("A" & ActiveCell.Row + 1).Activate
         'Paste the row daqta into current selected row
         ActiveSheet.Paste
         
         'Set default values of newly added row data
         'AddDefaults(blah blah)
         'MsgBox ("Defaults added")
    

    End Sub

As you can see, I am copying a cell in sheet2b into sheet1 using the AddData procedure.
However, I would now like to copy the entire row instead of a single cell. How do I do this?

The AddData procedure takes the cellrng variable from the loop that goes throug column A in sheet2.

Any help would be great.


Note: My VB skills are quite novice.

 

[VBAva]

you can use this to select a full row

Rows("1&quot.Select

this selects the whole of row 1
then selection.copy

 

[evild]

I know of this method, but I would like to use the row that the loop is currently on [defined by cellrng here]
I could add a counter in the loop i suppose.

 

[evild]

I had assumed that there would be a way to select the entire row using the cell that is currently selected. Maybe this is not the case.

 

[xlbo]

use cellrng.row
so rows(cellrng.row).entirerow.select
should work nicely for you

Also:
'Move to the next available empty row
Range("A1&quot.Activate
'Going to the last populated row
Selection.End(xlDown).Select
'and then down one
Range("A" & ActiveCell.Row + 1).Activate
could be changed to:
Range("A1&quot.Offset(Range("A1&quot.End(xlDown).Row, 0).Select

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[evild]

Thanks!! That has made things work very well now. I would never have got that by myself.

 

[RobBroekhuis]

Even simpler:

cellrng.entirerow.select
and
range("A1&quot.end(xldown).offset(1,0).select


Rob

 

[xlbo]

woo yay - Rob's back and correcting me already ;-)

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244