Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Tidy VBA Code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Tidy VBA Code

Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

Range("Q2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDate_EndValue),""Del"",""Keep"")"
ActiveCell.Offset(1, 0).Select
Loop
Range("Q2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
Do While ActiveCell = "Del"
ActiveCell.Offset(0, -16).Range("A1:Q1").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, 16).Select
Loop
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Range("Q:Q").ClearContents


Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 143
Default Tidy VBA Code
On Dec 6, 4:56*pm, vom wrote:
Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

* * * * Range("Q2").Select
* * * * Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDat*e_EndValue),""Del"",""Keep"")"
* * * * ActiveCell.Offset(1, 0).Select
* * * * *Loop
* * * * Range("Q2").Select
* * Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * * * Do While ActiveCell = "Del"
* * * * * * ActiveCell.Offset(0, -16).Range("A1:Q1").Select
* * * * * * Selection.Delete Shift:=xlUp
* * * * * * ActiveCell.Offset(0, 16).Select
* * * * * * Loop
* * ActiveCell.Offset(1, 0).Select
* * Loop
* * * * ActiveSheet.Range("Q:Q").ClearContents

Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul

As coded, you are paying a very tiny penalty in code execution time.
You don't need to Select in the loops. You can just loop over the
ranges.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,522
Default Tidy VBA Code
I would not use the apply formula and then delete due to formula.
something like

Sub foreachC()
For Each c In Cells(2, "q").Resize(Cells(Rows.Count,
"Q").End(xlUp).Row)
' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete
Next c
End Sub
'==========
On Dec 6, 3:56*pm, vom wrote:
Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

* * * * Range("Q2").Select
* * * * Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDat e_EndValue),""Del"",""Keep"")"
* * * * ActiveCell.Offset(1, 0).Select
* * * * *Loop
* * * * Range("Q2").Select
* Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * * * Do While ActiveCell = "Del"
* * * * * * ActiveCell.Offset(0, -16).Range("A1:Q1").Select
* * * * * * Selection.Delete Shift:=xlUp
* * * * * * ActiveCell.Offset(0, 16).Select
* * * * * * Loop
* * ActiveCell.Offset(1, 0).Select
* * Loop
* * * * ActiveSheet.Range("Q:Q").ClearContents

Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Tidy VBA Code
Thanks Don,

' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete
Didn't explain very clearly here, the start date and end date are
named values with date & time format.
Column P has date & time showing, and the formula deletes the dates
not inbetween these 2 values (deletes the whole table row A to P -
shiftup)

I got your formula working by adapting it to column P
Sub foreachC()
Sheets("Dollies - Shipment").Activate
For Each c In Cells(2, "p").Resize(Cells(Rows.Count,
"P").End(xlUp).Row)
If c.Offset(, 0) < ShipmentDate_StartValue Or c.Offset(, 0)
shipmentDate_EndValue Then Rows(c.Row).Delete
Next c

The problem is I can only delete columns A to Q, as there is data in R
to T, and your formula deletes the whole row. Can this be changed


On Wed, 7 Dec 2011 06:02:39 -0800 (PST), Don Guillett
wrote:

I would not use the apply formula and then delete due to formula.
something like

Sub foreachC()
For Each c In Cells(2, "q").Resize(Cells(Rows.Count,
"Q").End(xlUp).Row)
' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete
Next c
End Sub
'==========
On Dec 6, 3:56*pm, vom wrote:
Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

* * * * Range("Q2").Select
* * * * Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDat e_EndValue),""Del"",""Keep"")"
* * * * ActiveCell.Offset(1, 0).Select
* * * * *Loop
* * * * Range("Q2").Select
* Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * * * Do While ActiveCell = "Del"
* * * * * * ActiveCell.Offset(0, -16).Range("A1:Q1").Select
* * * * * * Selection.Delete Shift:=xlUp
* * * * * * ActiveCell.Offset(0, 16).Select
* * * * * * Loop
* * ActiveCell.Offset(1, 0).Select
* * Loop
* * * * ActiveSheet.Range("Q:Q").ClearContents

Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,522
Default Tidy VBA Code
On Dec 7, 12:44*pm, vom wrote:
Thanks Don,

' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
* c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete

Didn't explain very clearly here, *the start date and end date are
named values with date & time format.
Column P has date & time showing, and the formula deletes the dates
not inbetween these 2 values (deletes the whole table row A to P -
shiftup)

I got your formula working by adapting it to column P
Sub foreachC()
* * Sheets("Dollies - Shipment").Activate
For Each c In Cells(2, "p").Resize(Cells(Rows.Count,
"P").End(xlUp).Row)
If c.Offset(, 0) < ShipmentDate_StartValue Or c.Offset(, 0)
shipmentDate_EndValue Then Rows(c.Row).Delete
Next c

The problem is I can only delete columns A to Q, as there is data in R
to T, and your formula deletes the whole row. *Can this be changed

On Wed, 7 Dec 2011 06:02:39 -0800 (PST), Don Guillett







wrote:
I would not use the apply formula and then delete due to formula.
something like

Sub foreachC()
For Each c In Cells(2, "q").Resize(Cells(Rows.Count,
"Q").End(xlUp).Row)
' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
* c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete
Next c
End Sub
'==========
On Dec 6, 3:56 pm, vom wrote:
Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

Range("Q2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDat e_EndValue),""Del"",""Keep"")"
ActiveCell.Offset(1, 0).Select
Loop
Range("Q2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
Do While ActiveCell = "Del"
ActiveCell.Offset(0, -16).Range("A1:Q1").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, 16).Select
Loop
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Range("Q:Q").ClearContents

Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul
*'Then Rows(c.Row).Delete
Then range(c.row,"a"),cells(c.row,"q")).Delete
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,522
Default Tidy VBA Code
On Dec 7, 1:19*pm, Don Guillett wrote:
On Dec 7, 12:44*pm, vom wrote:







Thanks Don,

' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
* c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete

Didn't explain very clearly here, *the start date and end date are
named values with date & time format.
Column P has date & time showing, and the formula deletes the dates
not inbetween these 2 values (deletes the whole table row A to P -
shiftup)

I got your formula working by adapting it to column P
Sub foreachC()
* * Sheets("Dollies - Shipment").Activate
For Each c In Cells(2, "p").Resize(Cells(Rows.Count,
"P").End(xlUp).Row)
If c.Offset(, 0) < ShipmentDate_StartValue Or c.Offset(, 0)
shipmentDate_EndValue Then Rows(c.Row).Delete
Next c

The problem is I can only delete columns A to Q, as there is data in R
to T, and your formula deletes the whole row. *Can this be changed

On Wed, 7 Dec 2011 06:02:39 -0800 (PST), Don Guillett

wrote:
I would not use the apply formula and then delete due to formula.
something like

Sub foreachC()
For Each c In Cells(2, "q").Resize(Cells(Rows.Count,
"Q").End(xlUp).Row)
' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
* c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete
Next c
End Sub
'==========
On Dec 6, 3:56 pm, vom wrote:
Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

Range("Q2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDat e_EndValue),""Del"",""Keep"")"
ActiveCell.Offset(1, 0).Select
Loop
Range("Q2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
Do While ActiveCell = "Del"
ActiveCell.Offset(0, -16).Range("A1:Q1").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, 16).Select
Loop
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Range("Q:Q").ClearContents

Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul

*'Then Rows(c.Row).Delete
*Then range(c.row,"a"),cells(c.row,"q")).Delete
oops. Use
Then range(cells(c.row,"a"),cells(c.row,"q")).Delete
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Tidy VBA Code
On Wed, 7 Dec 2011 11:43:45 -0800 (PST), Don Guillett
wrote:

On Dec 7, 1:19*pm, Don Guillett wrote:
On Dec 7, 12:44*pm, vom wrote:







Thanks Don,

' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
* c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete

Didn't explain very clearly here, *the start date and end date are
named values with date & time format.
Column P has date & time showing, and the formula deletes the dates
not inbetween these 2 values (deletes the whole table row A to P -
shiftup)

I got your formula working by adapting it to column P
Sub foreachC()
* * Sheets("Dollies - Shipment").Activate
For Each c In Cells(2, "p").Resize(Cells(Rows.Count,
"P").End(xlUp).Row)
If c.Offset(, 0) < ShipmentDate_StartValue Or c.Offset(, 0)
shipmentDate_EndValue Then Rows(c.Row).Delete
Next c

The problem is I can only delete columns A to Q, as there is data in R
to T, and your formula deletes the whole row. *Can this be changed

On Wed, 7 Dec 2011 06:02:39 -0800 (PST), Don Guillett

wrote:
I would not use the apply formula and then delete due to formula.
something like

Sub foreachC()
For Each c In Cells(2, "q").Resize(Cells(Rows.Count,
"Q").End(xlUp).Row)
' not quite sure what you want here.
If c.offset(,-1)<ShipmentDate_StartValue or _
* c.offset(,-1)shipmentDate_EndValue) then rows(c.row).delete
Next c
End Sub
'==========
On Dec 6, 3:56 pm, vom wrote:
Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

Range("Q2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDat e_EndValue),""Del"",""Keep"")"
ActiveCell.Offset(1, 0).Select
Loop
Range("Q2").Select
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
Do While ActiveCell = "Del"
ActiveCell.Offset(0, -16).Range("A1:Q1").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, 16).Select
Loop
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Range("Q:Q").ClearContents

Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul

*'Then Rows(c.Row).Delete
*Then range(c.row,"a"),cells(c.row,"q")).Delete
oops. Use
Then range(cells(c.row,"a"),cells(c.row,"q")).Delete



Thanks Don

That deleted the columns ok.
Am just trying to fit it in with using the endtime & starttime. It's
not working as I expected.
The formula doesn't stop when it reaches a valid date

'For Each c In Cells(2, "P").Resize(Cells(Rows.Count,
"P").End(xlUp).Row)
'If c < ShipmentDate_StartValue Or c ShipmentDate_EndValue _
'Then Range(Cells(c.Row, "a"), Cells(c.Row, "q")).Delete
'Next c

ie. When c< shipmentDate_EndValue it still deletes the columns.
I will try to sort it out myself, and shout on here if I require help.

Thanks again.
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8
Default Tidy VBA Code
On Tue, 6 Dec 2011 16:27:29 -0800 (PST), James Ravenswood
wrote:

On Dec 6, 4:56*pm, vom wrote:
Hi all,

I am not very experienced in writing vba.
I have got the following code, put together by recording macros
mostly, and editing afterwards.

Is there a simpler way to construct the below code.

* * * * Range("Q2").Select
* * * * Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * ActiveCell.FormulaR1C1 = _

"=IF(OR('Pallet'!RC[-1]<ShipmentDate_StartValue,'Pallet'!RC[-1]ShipmentDat*e_EndValue),""Del"",""Keep"")"
* * * * ActiveCell.Offset(1, 0).Select
* * * * *Loop
* * * * Range("Q2").Select
* * Do While Not IsEmpty(ActiveCell.Offset(0, -1))
* * * * * * Do While ActiveCell = "Del"
* * * * * * ActiveCell.Offset(0, -16).Range("A1:Q1").Select
* * * * * * Selection.Delete Shift:=xlUp
* * * * * * ActiveCell.Offset(0, 16).Select
* * * * * * Loop
* * ActiveCell.Offset(1, 0).Select
* * Loop
* * * * ActiveSheet.Range("Q:Q").ClearContents

Data resides in column a to p
formula above check the data, populates column q, and acts on it
depending on value.

Cannot delete the whole row as data is on further columns (unable to
change this)

Thanks in advance
edul

As coded, you are paying a very tiny penalty in code execution time.
You don't need to Select in the loops. You can just loop over the
ranges.


Thanks for that James. I am running the live code as done, but am
trying to learn vba a bit better now.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Tidy up multiple find and replace code PSM[_10_] Excel Worksheet Functions 2 April 6th 09 02:00 PM
Loop to tidy up my code? Craig Handley[_2_] Excel Programming 2 February 6th 08 11:17 AM
Can anyone help me tidy up? drucey[_33_] Excel Programming 0 May 5th 06 04:47 PM
Goto misused: help to tidy Code davidm Excel Programming 3 December 20th 05 04:36 AM
smart & tidy code for many checkBox_Change() Fendic[_16_] Excel Programming 3 August 14th 05 02:52 PM


All times are GMT +1. The time now is 06:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement