deleting lines

**stevesunfold** · 07-14-2007, 02:08 PM

thats of great help but of no real help noob
although im grateful for your help i really dont understand formulas

**VBA Noob** · 07-14-2007, 02:20 PM

Hi stevesunfold,

Just add the code to a module in your workbook then use this forumla in cell C2 and drag down

=TEXT(A2,"dd/mm/yy")&" "&TEXT(B2,"HH:MM")

module.

Sub DeleteRows()
Dim Last, Cnt As Long
Dim Rng As Range
Last = Cells(Rows.Count, "C").End(xlUp).Row
Set Rng = Range("C2:C" & Last)
For Cnt = Last To 1 Step -1
If WorksheetFunction.CountIf(Rng, Cells(Cnt, "C").Value) > 1 Then
Cells(Cnt, "C").EntireRow.Delete
End If
Next Cnt
End Sub

The code will run from the last cell in Col C to C2 deleting the entirerow as it goes.

Non VBA would be use this in C2 and drag down

=IF(MAX(COUNTIF(C2:C20,C2:C20))>1,"Duplicates","No Duplicates")
Then filter on Duplicates and delete

HTH

VBA Noob

**stevesunfold** · 07-14-2007, 02:47 PM

vba noob

what can i say but youre a star

many many thanks
i did what you said and although i dont understand it it worked a treat

thanks so much
steve

**VBA Noob** · 07-14-2007, 02:48 PM

Glad it helped

VBA Noob

**stevesunfold** · 07-14-2007, 02:53 PM

it was more than a help it was fantstic i need to learn excel more but i just cant take it in like some.

one more question please is there anyway i can extract info from one cell
for example

in cell b1 i have a time followed by a track

ie

2:20 lingfield is there anyway i can get the lingfield part of the cell into say column c?

regards

**VBA Noob** · 07-14-2007, 02:58 PM

Maybe

=TRIM(MID(B1,FIND(" ",B1),9999))

VBA Noob

**stevesunfold** · 07-14-2007, 03:12 PM

once again fantastic
does exactly that but still leaves the time and the track in the first cell b1 where i did want it to be just the time

gonna save then go have a play

will let you know how i get on

many many thanks
if you are into horse racing let me know mate

deleting lines

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions