OK, now I'm confused. In the initial request, I thought you wanted to delete
the cells that had no entry. Now you want to delete the adjacent columns.
Give me some specific examples.
If the last entry is in Row 40, Specifically which cells do you want to
delete?
Thanks,
Barb Reinhardt
"James C." wrote:
> lRow is the first empty row of data. I want to delete the adjacent 24 columns
> (which happen to be columns 44 through 68). I want to delete all the way to
> the bottom of the page. The columns to be deleted never change, just the
> starting point.
>
> "Barb Reinhardt" wrote:
>
> > I assumed that lRow was the last row of data in the column of interest. What
> > do you get for lRow when you run it and how many more rows do you want to
> > use. Are there columns of data adjacent to it in the same row? If so, which
> > ones.
> > --
> > HTH,
> > Barb Reinhardt
> >
> >
> >
> > "James C." wrote:
> >
> > > Barb,
> > >
> > > Here is what I have so far (with your inclusions).... This gets me to the
> > > row I need to be at. Now I want it to delete columns 44 through 68 from this
> > > row down.
> > >
> > > Sub Test()
> > >
> > > Dim myWS As Worksheet
> > > Dim myRange As Range
> > >
> > > Set myWS = Sheets("Annual Data")
> > > Set myRange = myWS.Cells(7, 43)
> > >
> > > lrow = myRange.Parent.Cells(myRange.Parent.Rows.Count,
> > > myRange.Column).End(xlUp).Row
> > > lrow = lrow - 1
> > >
> > > End sub
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > Try this
> > > >
> > > > Sub Test()
> > > >
> > > > Dim myDeleteRange As Range
> > > > Dim myWS As Worksheet
> > > > Dim myRange As Range
> > > > Dim r As Range
> > > >
> > > > Set myWS = Sheets("Data")
> > > >
> > > > Set myDeleteRange = Nothing
> > > > Set myRange = myWS.Cells(7, 43)
> > > >
> > > > lrow = myRange.Parent.Cells(myRange.Parent.Rows.Count,
> > > > myRange.Column).End(xlUp).Row
> > > >
> > > > If lrow > myRange.Row Then
> > > > Set myRange = myRange.Resize(lrow - myRange.Row + 1, 1)
> > > > Debug.Print myRange.Address
> > > >
> > > > End If
> > > > For Each r In myRange
> > > > If IsEmpty(r) Then
> > > > If myDeleteRange Is Nothing Then
> > > > Set myDeleteRange = r
> > > > Else
> > > > Set myDeleteRange = Union(myDeleteRange, r)
> > > > End If
> > > > End If
> > > >
> > > > Next r
> > > > If Not myDeleteRange Is Nothing Then
> > > > myDeleteRange.Delete Shift:=xlUp
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > > --
> > > > HTH,
> > > > Barb Reinhardt
> > > >
> > > >
> > > >
> > > > "James C." wrote:
> > > >
> > > > > Hi all,
> > > > >
> > > > > I am trying to delete a range that will vary depending on how much data is
> > > > > in a previous column. For instance I have a data tab that has a query on it.
> > > > > I never know how much data will come from that query but I do know that I
> > > > > when there is no data at the bottom of the query I would like to delete the
> > > > > five cells to the right of it down to the bottom of the page. This is what I
> > > > > have so far, but I don't know how to insert a variable into a range.
> > > > >
> > > > > x = 7
> > > > > Do while Sheets("Data").Cells(x, 43) = ""
> > > > > Range("x, 48").Select -- (Not sure on this part)
> > > > > Selection.Delete Shift:=xlUp
> > > > >
> > > > > x = x+1
> > > > > Loop
> > > > >
|
Similar Threads
