Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

merged fields

  Asked By: Ashan    Date: Oct 27    Category: MS Office    Views: 679
  

i have created a pricing form and in my code at the end i have
got the following code

'to delete rows when options not choosen in order to tidy up!
'this has to first sort the data to get all the blank rows together

Range("A5:C15").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D15").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = 0 Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop

'this worked fine until i amended the template and merged column a
and b together, the coding fell over and said something about the
merged fields have to be the same size, i made sure they were and it
still doesnt work.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Russell Burns     Answered On: Oct 27

I never use a Do While or Do Until anymore. To much chance for
infinite loops. I use For Next. For example if you know the rows  are
5 to 15, in a delete  run a backwards For Next. As for the merged
cells, maybe removing xlup will work. Or unmerging them and
remerging them. Anyway heres a for next loop:

Range("A5:C15").Select
Selection.Sort Key1:=Range("A5"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Dim i as integer, Val1 as string

For i = 15 to 5 step -1
Val1 = Range("D"& i).value
if Val1 = 0 then
range("D"& i).EntireColumn.Delete 'shift:=xlUp
end if
next i

 
Didn't find what you were looking for? Find more on merged fields Or get search suggestion and latest updates.




Tagged:    

 
 
 
 

Related Post