Selecting entire rows contents

[GB900180]

Guys,

First time on here & I'm a bit of a VBA newbie. I'm trying to construct a pretty simple macro to scroll down through the contents of a worksheet and where a given cell is "" to take the entire rows contents to another sheet. So far I'm up to here ...


RownumberEndWorksheet = 14
Do Until Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet, 5) = ""
If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet, 13) = "" Then

At this point I've nominated all the individual cells to be taken from "All Transfer Data F24.89_3" by specifying cells(RownumberEndWorksheet, applicable column ref). This is tedious as there's lots. Then I look to the sheet to paste data & find the next available line as follows ;

RownumberNext = 5
Do Until Sheets("Paste Data").Cells(RownumberNext, 2) = ""
RownumberNext = RownumberNext + 1
Loop

Worksheets("Paste Data").Cells(RownumberNext, 2) = VarBarcode

etc.



Please can someone show me how to select the entire contents of a row when I've found one where:

If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet, 13) = ""

and then get the data in to the paste sheet.


I'm sorry this is probably childs play to someone with some experience but I'm struggling here. Much appreciated in advance!!!!!!!!!!!!!!!!!!!!

[Tim Williams]

Try this (untested but compiled OK)

Sub Test()

Dim shtCopy As Worksheet
Dim shtPaste As Worksheet
Dim rowcopy As Long, numrows As Long

Set shtCopy = ThisWorkbook.Sheets("All Transfer Data F24.89_3")
Set shtPaste = ThisWorkbook.Sheets("Paste Data")

rowcopy = 14
numrows = shtCopy.Rows.Count

Do While shtCopy.Cells(rowcopy, 5).Value <> ""

If shtCopy.Cells(rowcopy, 13).Value = "" Then

shtCopy.Rows(rowcopy).Copy _
Destination:=shtPaste.Cells(numrows,
2).End(xlUp).Offset(1, -1)

End If

rowcopy = rowcopy + 1
Loop


End Sub




Tim

"GB900180" <[email protected]> wrote in
message news:[email protected]...
>
> Guys,
>
> First time on here & I'm a bit of a VBA newbie. I'm trying to
> construct a pretty simple macro to scroll down through the contents of
> a worksheet and where a given cell is "" to take the entire rows
> contents to another sheet. So far I'm up to here ...
>
>
> RownumberEndWorksheet = 14
> Do Until Sheets("All Transfer Data
> F24.89_3").Cells(RownumberEndWorksheet, 5) = ""
> If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet,
> 13) = "" Then
>
> At this point I've nominated all the individual cells to be taken from
> "All Transfer Data F24.89_3" by specifying
> cells(RownumberEndWorksheet, applicable column ref). This is tedious
> as there's lots. Then I look to the sheet to paste data & find the
> next available line as follows ;
>
> RownumberNext = 5
> Do Until Sheets("Paste Data").Cells(RownumberNext, 2) = ""
> RownumberNext = RownumberNext + 1
> Loop
>
> Worksheets("Paste Data").Cells(RownumberNext, 2) = VarBarcode
>
> etc.
>
>
>
> Please can someone show me how to select the entire contents of a row
> when I've found one where:
>
> If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet,
> 13) = ""
>
> and then get the data in to the paste sheet.
>
>
> I'm sorry this is probably childs play to someone with some experience
> but I'm struggling here. Much appreciated in
> advance!!!!!!!!!!!!!!!!!!!!
>
>
> --
> GB900180
> ------------------------------------------------------------------------
> GB900180's Profile:
> http://www.excelforum.com/member.php...o&userid=30423
> View this thread: http://www.excelforum.com/showthread...hreadid=501905
>

[GB900180]

Tim,

Cheers for the reply mate. I figured a solution myself as follows works at the moment ...

Dim VarPaste, VarBarcode As String
Dim RownumberEndWorksheet, RownumberNext As Integer

RownumberEndWorksheet = 14
Do Until Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet, 5) = ""
'
'Select only those rows where a transfer form has not been returned
'
If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet, 13) = "" Then
'
'Define "VarPasteEntireRow"
'
VarPaste = Sheets("All Transfer Data F24.89_3").Rows(RownumberEndWorksheet).EntireRow
'
'Find an empty row on the destination worksheet (exception report)
'
RownumberNext = 5
Do Until Sheets("Paste Data").Cells(RownumberNext, 5) = ""
RownumberNext = RownumberNext + 1
Loop
'
'Copy and paste the applicable data in to the desired format
'
Sheets("Paste Data").Rows(RownumberNext).EntireRow = VarPaste

End If
'
'Move to the next row on the "All Transfer Data F24.89_3" worksheet
'
RownumberEndWorksheet = RownumberEndWorksheet + 1
'
'Loop and end the sub
'
Loop
End Sub




My only remaining question is that if I remove the "VarBarcode" from my declaring variables section I get an error and the macro debugs. Guessing the VarPaste didn't ought to be string as am selecting a whole row - should it be an object? It works at the moment & i need it pronto so don't want to change anything? Suggestions? Cheers

[Tim Williams]

VarPaste is not a string: if you declare two variables on the same line like
this

Dim Var1, Var2 as String

then only Var2 is a string. By default Var1 is a variant-type (good choice
actually).
When you run your code it picks up the "value" of the copy range: this takes
the form of a 2-D array.

When you remove VarBarcode it has the effect of declaring VarPaste as
String, which is the wrong type for your needs.

Cheers,

Tim


"GB900180" <[email protected]> wrote in
message news:[email protected]...
>
> Tim,
>
> Cheers for the reply mate. I figured a solution myself as follows
> works at the moment ...
>
> Dim VarPaste, VarBarcode As String
> Dim RownumberEndWorksheet, RownumberNext As Integer
>
> RownumberEndWorksheet = 14
> Do Until Sheets("All Transfer Data
> F24.89_3").Cells(RownumberEndWorksheet, 5) = ""
> '
> 'Select only those rows where a transfer form has not been returned
> '
> If Sheets("All Transfer Data F24.89_3").Cells(RownumberEndWorksheet,
> 13) = "" Then
> '
> 'Define "VarPasteEntireRow"
> '
> VarPaste = Sheets("All Transfer Data
> F24.89_3").Rows(RownumberEndWorksheet).EntireRow
> '
> 'Find an empty row on the destination worksheet (exception report)
> '
> RownumberNext = 5
> Do Until Sheets("Paste Data").Cells(RownumberNext, 5) = ""
> RownumberNext = RownumberNext + 1
> Loop
> '
> 'Copy and paste the applicable data in to the desired format
> '
> Sheets("Paste Data").Rows(RownumberNext).EntireRow = VarPaste
>
> End If
> '
> 'Move to the next row on the "All Transfer Data F24.89_3" worksheet
> '
> RownumberEndWorksheet = RownumberEndWorksheet + 1
> '
> 'Loop and end the sub
> '
> Loop
> End Sub
>
>
>
>
> My only remaining question is that if I remove the "VarBarcode" from my
> declaring variables section I get an error and the macro debugs.
> Guessing the VarPaste didn't ought to be string as am selecting a whole
> row - should it be an object? It works at the moment & i need it pronto
> so don't want to change anything? Suggestions? Cheers
>
>
> --
> GB900180
> ------------------------------------------------------------------------
> GB900180's Profile:
> http://www.excelforum.com/member.php...o&userid=30423
> View this thread: http://www.excelforum.com/showthread...hreadid=501905
>