Remove rows in huge data array

[Artem]

Hi,

I need some help with VBA programming. I have a huge array with financial
data: daily data for 7 last years. The first column (A:A) in the array is
dates in format dd.mm.yyyy, next few columns contains different data. What I
need is to go through dates, find weekends and delete rows with weekends (not
just clear content of rows but delete/cut them).

Thanks in advance!

 

[Gary''s Student]

Try this:

Sub noweekend()
Dim n As Long, d As Date
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = n To 1 Step -1
d = Cells(i, 1).Value
dayIs = Format(d, "ddd")
If dayIs = "Sat" Or dayIs = "Sun" Then
Cells(i, 1).EntireRow.Delete
End If
Next
End Sub

This assumes that the values are "real" dates.

 

[JLGWhiz]

Give this a try:

Sub Titles()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
With ActiveSheet
weDay = Format(.Cells(i, 1).Value, "w")
If weDat = 1 Or weDay = 7 Then
.Cells(i, 1).EntireRow.Delete
End If
End With
Next
End Sub

 

[Artem]

Thanks, but it doesn't work for me. Nothing happens then I'm running the
macro. What does "real date" mean? I've just used date format for the date
column, is it enough? I'musing Office 2007 btw.

 

[Artem]

It works but partly. Macro removed all Saturdays but not Sundays:-( I'm using
Office 2007, btw.

Thanks.

 

[Gary''s Student]

If weDay = 1 Or weDay = 7 Then

 

[Artem]

I'm feeling myself completely stupid

Thanks a lot both of you!!! You saved me lots of time.

 

[JLGWhiz]

Thanks GS, someone needs to come up with a spell check for VBA. I suppose
Option Explicit would have caught that.