Deleting lines that are not needed.
 

On Sheet1 I have data running from A1-E1 with the number of lines o
data is undetermined so you need to look for the last row.

What I would like to do is check to see if there is a value in B1 or C
and if so- copy the line and move it to Sheet2. I want to do this fo
the remainder of the sheet moving copying lines with values in B or
to the next line on Sheet2.

Any help in code would be very much appreciated

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=54062

Deleting lines that are not needed.
 

heres one way

Option Explicit

Sub CopyData()
Dim wS1 As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long
Dim l4Row As Long
Dim lNxtRow As Long

Set wS1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")

lRow = wS1.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
lNxtRow = ws2.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
For l4Row = 1 To l4Row Step 1
If wS1.Cells(l4Row, "b").Value < "" _
Or wS1.Cells(l4Row, "c").Value < "" Then
lNxtRow = lNxtRow + 1
wS1.Range("a" & l4Row & ":e" & l4Row).Copy ws2.Cells(lNxtRow, "a")
End If
Next l4Row
End Su

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=54062

Deleting lines that are not needed.
 

Perhaps something like this:

Dim rng as Range
with worksheets("sheet1")
set rng = columns(B:C).specialcells(xlconstants)
End with
if not rng is nothing then
rng.entirerow.copy Destination:=Worksheets("Sheet2").Range("A1")
End if

--
Regards,
Tom Ogilvy


"sungen99" wrote:


On Sheet1 I have data running from A1-E1 with the number of lines of
data is undetermined so you need to look for the last row.

What I would like to do is check to see if there is a value in B1 or C1
and if so- copy the line and move it to Sheet2. I want to do this for
the remainder of the sheet moving copying lines with values in B or C
to the next line on Sheet2.

Any help in code would be very much appreciated.


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Deleting lines that are not needed.
 

Thank you for your quick reply. when i plug in this macro i get stoped
here.

Cant execute code break is the error.

lNxtRow = ws2.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Deleting lines that are not needed.
 

Selfish bump. :(


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Deleting lines that are not needed.
 

Can anyone help me please de-bug either of the codes? I cant seem to ge
either of them to work

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=54062

Deleting lines that are not needed.
 

I tried to use the macro recorder to sort by col B, then by C. It does
what I need to just copy the top X lines that contain data in the col B
and C and move it onto sheet2.

It looks kinda like im right back where I was.

I’m sure this code is difficult and it would figure that I’m actually
doing a favor for someone on this one as apposed to it being for me.

Your help is much appreciated.


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Deleting lines that are not needed.
 

Being a pest is the last thing I want to be :) and therefore with is my
last bother with this thread. If someone can help me that’s great- I
know I am asking a lot and all with the coding with this. ;)

Thanks again,


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Deleting lines that are not needed.
 

If sheet 2 is blank then code will error


Try this

Option Explicit

Sub CopyData()
Dim wS1 As Worksheet
Dim ws2 As Worksheet
Dim lRow As Long
Dim l4Row As Long
Dim lNxtRow As Long

Set wS1 = Sheets("sheet1")
Set ws2 = Sheets("sheet2")

lRow = wS1.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
On Error Resume Next
lNxtRow = ws2.Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
On Error Goto 0
For l4Row = 1 To l4Row Step 1
If wS1.Cells(l4Row, "b").Value < "" _
Or wS1.Cells(l4Row, "c").Value < "" Then
lNxtRow = lNxtRow + 1
wS1.Range("a" & l4Row & ":e" & l4Row).Copy ws2.Cells(lNxtRow, "a")
End If
Next l4Row
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Deleting lines that are not needed.
 

Thank you so much for getting back to me. However when I use the code
in my application nothing happens.

Im wondering if its not doing what im looking for.

Im looking for it to take the data on sheet1 and see if there is a
value in b or c. if there is copy the line from a – f to sheet 2 and
move on until the end of the sheet.

Thanks again for your help in this.


--
sungen99
------------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...fo&userid=9144
View this thread: http://www.excelforum.com/showthread...hreadid=540628

Deleting lines that are not needed.
 

I am just wondering if don’t have a function turned on. You all hav
been so kind in giving me code yet none of the programs end correctly.
It seems like when the macro is run. It just sits there and don
nothing

--
sungen9
-----------------------------------------------------------------------
sungen99's Profile: http://www.excelforum.com/member.php...nfo&userid=914
View this thread: http://www.excelforum.com/showthread.php?threadid=54062

Deleting lines that are not needed.
 

Hi,

when I read the header, you suggest that you would like to delete lines
with a content which is not of use for you. For this pupose I created
this piece of code. It may not be very sophisticated, but it doe what
it is supposed to: it eliminates those lines where a specific column
contains 0 as value.

Option Explicit
Sub NullRowsDelete()

Dim NullColumn As Integer 'Number of Column to be
tested for zero
Dim StartRow As Integer 'Number of Row where the
data range starts
Dim StartColumn As Integer 'Number of column where
the data range starts
Dim EndRow As Integer 'Number of Row wher the
data range ends
Dim Gross As Integer 'initial zoom factor
Dim N As Integer 'Variable
Dim Box As String 'message box
Dim Message As String
Dim ZU As String '

ZU = Chr(10)
Message = "This deletes all Rows, where the relevant
cells are zero or empty." + ZU + _
"In the next box you'll be asked for the number of
the Column which contains these relevant cells." + ZU + ZU + _
"The Cursor has to be placed in the left top corner
of the data range.." + ZU + _
"If this is all right, accept, otherwise you can
correct this."

Box = MsgBox(Message, vbYesNo, "Information")
If Box = vbNo Then
Exit Sub
End If
StartRow = Selection.Row
StartColumn = Selection.Column

Gross = ActiveWindow.Zoom
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown 'Include supportive
rows
Cells(StartRow, 1).Select
For N = 1 To 25
Cells(StartRow, N).Select
Selection.Value = N 'include numbering for
orientation
Next N
ActiveCell.EntireRow.Select
Selection.Font.Size = 14
Cells(StartRow, StartColumn + 1).Select
ActiveWindow.Zoom = 50

Message = "Please enter the number of the relevant
column" + _
" (see supportive numbers above the data range)."
NullColumn = Application.InputBox(Message, _
"Input of Column Number", 1)

Application.ScreenUpdating = False

Selection.EntireRow.Delete 'Eliminate row with
supportive numbers
ActiveWindow.Zoom = Gross
Cells(StartRow, StartColumn).Select
Selection.End(xlDown).Select
EndRow = Selection.Row

Cells(StartRow, NullColumn).Select 'jumps to the
first row in the range into the column to be tested

For N = 1 To (EndRow - StartRow)
If Selection.Value = 0 Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next

Application.ScreenUpdating = True


End Sub

Udo