I am trying to write a macro that will take a file, open it up then based on conditions in 3 colums delete the row. So if the value in Colum Q,T & W is zero then that row will be deleted. All three columns have to be 0.
so far I have:
Sub Text_File_to_Excel()
'
' Text_File_to_Excel Macro
' Macro recorded 4/13/2004 by C4TXL
'
'
myFile = Application.GetOpenFilename("Text Files,*.txt")
Workbooks.OpenText _
FileName:=myFile, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:= _
Array(Array(0, 1), Array(8, 1), Array(19, 1), Array(49, 1), Array(50, 1), Array(80, 1), _
Array(81, 1), Array(111, 1), Array(141, 1), Array(171, 1), Array(173, 1), Array(183, 1), _
Array(193, 1), Array(203, 1), Array(212, 1), Array(221, 1), Array(230, 1), Array(239, 1), _
Array(248, 1), Array(257, 1), Array(266, 1), Array(275, 1), Array(284, 1), Array(293, 1), _
Array(295, 1), Array(297, 1), Array(299, 1), Array(301, 1))
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Cells.Replace What:=",", Replacement:=" ", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False
myFile1 = Application.GetSaveAsFilename("*.xls")
ActiveWorkbook.SaveAs FileName:=myFile1, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Column ("X:AA").select
Selection.delete shift:=xltoleft
End Sub
I started to write an if then statement to do the 3 columns:
If Columns("Q:Q" & "t:t" & "w:w") = 0 Then
Rows(Rows).Delete
End If
but I quickly realized I had no idea how to write it. am I on the right track do I need to throw in an else If - 0 then delete else leave I am such a novice at this. I was supposed to take a class for my new job but the class got postponed and now I am hung out to dry for the moment.
Thanks,
Mary
VBA Excel Macro to delete rows based on column conditions
brewewa
MusicMan756
Hi Mary
No, the line ..
lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
actually works out the last row number and puts the value in lngLastRow.
PeterMo.
Sudeesh
Hi Mary
You will need to work through the rows individually to know whether you need to delete them. The following is one way of doing this ...
' define column numbers (for easier reading)
Const colQ As Long = 17
Const colT As Long = 20
Const colW As Long = 23
Dim lngRow As Long
Dim lngLastRow As Long
' what is the last row number on the sheet
lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
' OK, now work through the rows
lngRow = 1
Do While lngRow <= lngLastRow
If Cells(lngRow, colQ) = 0 And _
Cells(lngRow, colT) = 0 And _
Cells(lngRow, colW) = 0 Then
Cells(lngRow, 1).EntireRow.Delete
lngLastRow = lngLastRow - 1 ' one less row
Else
lngRow = lngRow + 1 ' move to next row
End If
Loop
Hope this helps
PeterMo.