adding delete to a vba in excel 2010

fvisions · Aug 3, 2018

I am looking to add the section below for the DimlRow as long...to delete rows from an existing file, save and close the file but the file only opens then stops without any error message.
‘Open desired file to format the data
' Delete row for hour 2 for DST 23 hour
ChDrive "L:\OtherImports\Supplier_Loads"
ChDir "L:\OtherImports\Supplier_Loads"
Do

fName = Application.GetOpenFilename
Loop Until fName <> False
Workbooks.Open Filename:=fName
Dim lRow As Long
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 22
For x = lRow To 2 Step -26
With Rows(x)
.Delete
End With
Next x
ActiveSheet.Range("A1").Select
ActiveWorkbook.SaveAs Filename:="L:\OtherImports\Supplier_Loads\Suppld" & DataDate & ".csv" _
, FileFormat:=xlCSVMSDOS, CreateBackup:=False
Workbooks("Suppld" & DataDate & ".CSV").Close saveChanges:=True
End Function

yky · Aug 3, 2018

Did you step through the code? What's the value of lRow?

fvisions · Aug 6, 2018

Column a is the hours 1 - 24 and column b is the number zero

[FONT=&quot]Meter[/FONT]
[FONT=&quot]Daily[/FONT]
[FONT=&quot]***[/FONT]
[FONT=&quot]6[/FONT]	[FONT=&quot]1245[/FONT]	[FONT=&quot]LOAD[/FONT]	[FONT=&quot]5/21/2018[/FONT]
[FONT=&quot]1[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]2[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]3[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]4[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]5[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]6[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]7[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]8[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]9[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]10[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]11[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]12[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]13[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]14[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]15[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]16[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]17[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]18[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]19[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]20[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]21[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]22[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]23[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]24[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]***[/FONT]
[FONT=&quot]6[/FONT]	[FONT=&quot]1246[/FONT]	[FONT=&quot]LOAD[/FONT]	[FONT=&quot]5/21/2018[/FONT]
[FONT=&quot]1[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]2[/FONT]	[FONT=&quot]0[/FONT]
[FONT=&quot]3[/FONT]	[FONT=&quot]0[/FONT]

Fluff · Aug 6, 2018

Are you trying to delete the rows where col A contains the *** that you have shown?
If so does the actual data contain ***, if not what is in those cells?

fvisions · Aug 6, 2018

I am looking to add the lines of code in read to the rest of the code below. The code in yellow goes to the end of the data set in a template and counts up to delete hour 2 for each 24 hour set. The code not in red is the original code that sets up the template file. I hope this helps.

Open desired file to format the data
' Delete row for hour 2 for DST 23 hour
ChDrive "L:\OtherImports\Supplier_Loads"
ChDir "L:\OtherImports\Supplier_Loads"
Do

fName = Application.GetOpenFilename
Loop Until fName <> False
Workbooks.Open Filename:=fName
Dim lRow As Long
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row - 22
For x = lRow To 2 Step -26
With Rows(x)
.Delete
End With
Next x
ActiveSheet.Range("A1").Select
ActiveWorkbook.SaveAs Filename:="L:\OtherImports\Supplier_Loads\Suppld" & DataDate & ".csv" _
, FileFormat:=xlCSVMSDOS, CreateBackup:=False
Workbooks("Suppld" & DataDate & ".CSV").Close saveChanges:=True
End Function

Example of template setup file before the deletion of hour 2

Meter
Daily
***
6	1245	LOAD	5/21/2018
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
10	0
11	0
12	0
13	0
14	0
15	0
16	0
17	0
18	0
19	0
20	0
21	0
22	0
23	0
24	0
***
6	1246	LOAD	5/21/2018
1	0
2	0
3	0

Fluff · Aug 6, 2018

Are those numbers in col A hard values, or formulae?

Fluff · Aug 6, 2018

Assuming col A is values not formulae try

Code:

Sub DelHour2()
   With Range("A:A")
      .Replace 2, True, xlWhole, , , , False
      .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
   End With
End Sub

fvisions · Aug 6, 2018

I have a new problem now, the get open file command is giving me a runtime error 1004 (application-defined or object defined) the red font rows of code below worked before I updated the code you gave me for the deleting portion but I am not well versed in VBA so I dont know if one has to do with the other. Please see updated coded below.

Function RunProg8()
'Open desired file to format the data
' Delete row for hour 2 for DST 23 hour
ChDrive "L:\OtherImports\Supplier_Loads"
ChDir "L:\OtherImports\Supplier_Loads"
Do
fName = Application.GetOpenFilename
Loop Until fName <> False
Workbooks.Open Filename:=fName
Call DelHour2
ActiveSheet.Range("A1").Select
ActiveWorkbook.SaveAs Filename:="L:\OtherImports\Supplier_Loads\Suppld" & DataDate & ".csv" _
, FileFormat:=xlCSVMSDOS, CreateBackup:=False
Workbooks("Suppld" & DataDate & ".CSV").Close saveChanges:=True
End Function

Sub DelHour2()
With Range("A:A")
.Replace 2, True, xlWhole, , , , False
.SpecialCells(xlConstants, xlLogical).EntireRow.Delete
End With
End Sub

Fluff · Aug 6, 2018

There's no reason why that should fail, just because of the modified code, but try

Code:

Function RunProg8()
'Open desired file to format the data
' Delete row for hour 2 for DST 23 hour
ChDrive "L:"
ChDir "L:\OtherImports\Supplier_Loads"
Do
fname = Application.GetOpenFilename
Loop Until fname <> False
Workbooks.Open Filename:=fname
With Range("A:A")
.Replace 2, True, xlWhole, , , , False
.SpecialCells(xlConstants, xlLogical).EntireRow.Delete
End With

ActiveSheet.Range("A1").Select
ActiveWorkbook.SaveAs Filename:="L:\OtherImports\Supplier_Loads\Suppld" & DataDate & ".csv" _
, FileFormat:=xlCSVMSDOS, CreateBackup:=False
Workbooks("Suppld" & DataDate & ".CSV").Close saveChanges:=True
End Function

fvisions · Aug 6, 2018

changed to the advised code you have and recieved the same error. I guess trying to update someone elses code is not going to work and i will work on creating a new one. Thank you for your assistance.

adding delete to a vba in excel 2010

fvisions

Board Regular

Excel Facts

yky

Well-known Member

fvisions

Board Regular

Fluff

MrExcel MVP, Moderator

fvisions

Board Regular

Fluff

MrExcel MVP, Moderator

Fluff

MrExcel MVP, Moderator

fvisions

Board Regular

Fluff

MrExcel MVP, Moderator

fvisions

Board Regular

Similar threads

Forum statistics

Share this page

adding delete to a vba in excel 2010

Board Regular

Excel Facts

Well-known Member

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock