Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove rows in huge data array
If weDay = 1 Or weDay = 7 Then
-- Gary''s Student - gsnu2007k |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove/edit a huge page number in the middle of a workshe | Excel Discussion (Misc queries) | |||
"Add/Remove Rows Code" adds rows on grouped sheets, but won't remove rows. | Excel Programming | |||
Remove Blank and Non Data Rows | Excel Discussion (Misc queries) | |||
Introducing rows in between every observation for huge panel data sets. | Excel Programming | |||
How to conditionally remove rows with data | Excel Worksheet Functions |