Dove wrote:
> glennmasters@hotmail.com wrote:
> > I have data in cells in rows C,D,AF&BL Im copy and pasting to another
> > worksheet. What im trying to do is if there is not data in cells in row
> > AF&BL I dont want it to copy the column onto the new worksheet. Cells
> > in row C&D will always have data in it.
> >
> > I.E 1) column no 3 has data in row C&D but NO data in AF & BL . I do
> > not want any data in that column(inc columns c&d) copied over.
> > 2) column no4 has data in row C&D and also data in either AF&BL this
> > one I DO want copied over.
> >
> > When I mean copied over I mean the data in cells C,D,AF&BL.
>
> If I have what you are looking for straight (I was left a little
> confused as to what you are calling rows and columns in the second
> paragraph), something like the following is what you are looking for:
>
> ==================================
>
> ' Substitue the column letters in [ ] for the numeric equivalent (i.e.
> C = 3)
>
> Dim i as Integer
> Dim c as Integer
> Dim wsData as Worksheet
> Dim wsPaste as Worksheet
>
> Set wsData = Worksheets("[name of sheet copying from]")
> Set wsPaste = Worksheets("[name of sheet pasting to]")
>
> c = [the starting row number on the sheet you are pasting to, just use
> "i" if you don't care about empty rows]
>
> wsData.Activate
>
> For i = 1 to ActiveSheet.UsedRange.Rows.Count ' Change 1 to 2 if you
> have a header row
>
> If (Cells(i,[AF].Value <> "") Or (Cells(i,[BL].Value <> "") Then
> Rows(i).Select
> Selection.Copy
> wsPaste.Activate 'I am doing the paste part from memory, may
> be off...
> Rows(c).Select
> ActiveSheet.Paste
> c = c + 1
> wsData.Activate
> End If
> Next i
>
> Set wsData = Nothing
> Set wsPaste = Nothing
>
> ==============================
>
> You may want to set the screen updating to false before going into the
> for loop and back to true after to loop unless you want Excel to blink
> around as it steps through the loop, or you may want to use the status
> bar to count the row you are working on (displaying the value of i) to
> keep track of where it is...
>
> David



Many Thanks

GLENN