delete rows

[scottie1]

Hi there

I have spreadsheet with dates in column B:B. The problem is I must have full months (example 01/04/2004 to 30/04/2004)in the column. what i want my code to do is find the first month in column B:B with a date that starts 01. then what ever is above it DELETED.

is this easy
cheers

 

[SkipVought]

Hi,

Wouldn't it be alot easier to just make all the dates from scratch?

Start with 1/1/2004 in B1, grab the copy handle and drag down to row 365.

Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[jcrater]

There's probably a cooler, faster way, but this should work:

Const c_COLUMN_B As Integer = 2
Const c_START_ROW As Integer = 2

Dim lngStartRow As Long
Dim lngCurrRow As Long
Dim strValue As String

lngCurrRow = c_START_ROW
strValue = Trim(Cells(lngCurrRow, c_COLUMN_B).Value)
Do Until Len(strValue) = 0
If CInt(Day(strValue)) = 1 Then
'delete previous rows
If lngCurrRow > c_START_ROW Then
'At least row found to delete
Range(Cells(c_START_ROW, c_COLUMN_B), Cells(lngCurrRow - 1, c_COLUMN_B)).Select
Selection.EntireRow.Delete
Exit Do
Else
'First value found is 1st of month; nothing to delete
Exit Do
End If
End If
lngCurrRow = lngCurrRow + 1
strValue = Trim(Cells(lngCurrRow, c_COLUMN_B).Value)
Loop

 

[scottie1]

Good Morning Folks


Skipvoight
No it wouldn't because I have figures adjacent to the dates.

Jcrater

This is lookin good but it doesn't seem to delete the rows above it. It does find the first of the month. do you know what is missing apart from the obvious.

cheers

 

[scottie1]

Hi again

Oops my mistake it works fine any chance of it being able to find a valid end of amonth and delete whats above it as well. If not dont worry.

cheers for all your help

scottie1