Remove rows in huge data array
 

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!

Remove rows in huge data array
 

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.
--
Gary''s Student - gsnu200830


"Artem" wrote:

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!

Remove rows in huge data array
 

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" wrote:

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!

Remove rows in huge data array
 

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.

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200830


"Artem" wrote:

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!

Remove rows in huge data array
 

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

Thanks.


"JLGWhiz" wrote:

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" wrote:

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!

Remove rows in huge data array
 

If weDay = 1 Or weDay = 7 Then
--
Gary''s Student - gsnu2007k

Remove rows in huge data array
 

I'm feeling myself completely stupid:-)

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


"Gary''s Student" wrote:

If weDay = 1 Or weDay = 7 Then
--
Gary''s Student - gsnu2007k

Remove rows in huge data array
 

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

"Gary''s Student" wrote:

If weDay = 1 Or weDay = 7 Then
--
Gary''s Student - gsnu2007k