Deleting Certain columns

  • Thread starter Thread starter Byers
  • Start date Start date
B

Byers

I have a huge template I'm working on, but some columns don't get used
these get marked "N/A" in the 13th row. The columns range from D-EB..
making the total range D13:EB13. I've been trying to use macros tha
you've given to people asking for help on row issuses, but it's been
few hours so now I need help. I want it to search through that rang
of the sheet "staffing schedule" and find the first cell with "N/A" an
then delete every column from that cell to column EB.

This is the Macro I've created as trying to manipulate one you sen
out... it doesn't work, but it could be easier for you to just fix i
if it is somewhere close.

Sub Delete_Rows()
' This macro deletes all rows on the active worksheet
' that have Employee, Spouse, Child, or is blank in column A.

Sheets("Staffing Schedule").Select
Range("D13:EB13").Select
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "N/A" Then
Set del = Union(del, cell)
End If
Next
On Error Resume Next
del.EntireColumn.Delete

End Sub

This wasn't the only one I tried, there were a lot, like I said, I'v
been trying to teach myself Visual Basic for most of the day... pleas
help, it will take a huge burden off my back.

-Mar
 
Try this :-


Code
-------------------

'----------------------------------------------
Sub Delete_Columns()
Dim Foundcell As Object
Dim WS As Worksheet
Dim MyRange As Range
'--
Application.Calculation = xlCalculationManual
Set WS = Worksheets("Staffing Schedule")
Set Foundcell = WS.Range("D13:EB13").Find(what:="N/A")
If Foundcell Is Nothing Then
MsgBox ("Could not find N/A")
Else
c = Foundcell.Column
Set MyRange = WS.Range(Cells(1, c), Cells(1, 132)).EntireColumn
MyRange.Delete
End If
Application.Calculation = xlCalculationAutomatic
End Sub
'-------------------------------------------------
 
I had a few runtime errors when I first started using it, but I was abl
to work most of them out. It still isn't working as it should though.
right now it will delete all the columns from E-EB, regardless of wher
the "N/A" is. Here is the altered form that was running for me, mayb
my changes made it do what it did, but it wouldn't run at all before
made them.

'------------------------------------------------------------------------------
Sub Delete_Columns()

Dim Foundcell As Object
Dim WS As Worksheet
Dim MyRange As Range
'--
Application.Calculation = xlCalculationManual
Set WS = Worksheets("Staffing Schedule")
Set Foundcell = WS.Range("D13:EB13").Find(what:="N/A")
If Foundcell Is Nothing Then
MsgBox ("Could not find N/A")
Else
c = Foundcell.Column
Set MyRange = WS.Range(WS.Cells(1, c), WS.Cells(1, 132))
MyRange.EntireColumn.Delete
End If
Application.Calculation = xlCalculationAutomatic
End Sub

'---------------------------------------------------------------------

I also discovered that there may be problems with linked sums that
have going across worksheets, so it might be better to just hide thes
cells, if that's possible. If not I'll have to find another way aroun
it. It is all for printability purposes, so anything will do even i
it's setting the width to 0.

*** Important, I think the problem could be occuring because "N/A" i
in the formula of the entire row, but not in the cell values, I'm goin
to try to change the code and get around it.


Thanks,

Mar
 
Back
Top