score:3
Accepted answer
if you delete rows in a loop, you need to start from the bottom. otherwise the row number of the rows below the actual deleted row decrease by one and i
gets increased by one (from the loop), so in sum you miss a line.
instead of
for i = 1 to 40
use
for i = 40 to 1 step -1
if worksheets("sheet4").cells(1, i) = 0 then
columns(i).entirecolumn.delete
end if
next
to loop backwards.
side note: (thx to @a.s.h)
you should use full qualified ranges/cells/rows/columns and never assume the worksheet. also declare all your variables using option explicit.
therefore
rows(1).insert shift:=xlshiftdown
'...
columns(i).entirecolumn.delete
should be
worksheets("sheet4").rows(1).insert shift:=xlshiftdown
'...
worksheets("sheet4").columns(i).entirecolumn.delete
so in sum we end up at
option explicit 'first line in module
public sub reorder()
dim i as long
dim wy as worksheet
set wy = worksheets("sheet4")
wy.rows(1).insert shift:=xlshiftdown
for i = 1 to 40
select case wy.cells(2, i)
case "physical location", "plc tag name", "test step1", "test step2", _
"test step3", "test step4", "test step5", "test step6", "test step7"
wy.cells(1, i) = 1
case else
wy.cells(1, i) = 0
end select
next i
for i = 40 to 1 step -1
if wy.cells(1, i) = 0 then
wy.columns(i).entirecolumn.delete
end if
next
end sub
or if we use only one loop which is a lot faster:
option explicit 'first line in module
public sub reorder()
dim i as long
dim wy as worksheet
set wy = worksheets("sheet4")
wy.rows(1).insert shift:=xlshiftdown
for i = 40 to 1 step -1
select case wy.cells(2, i)
case "physical location", "plc tag name", "test step1", "test step2", _
"test step3", "test step4", "test step5", "test step6", "test step7"
wy.cells(1, i) = 1
case else
wy.columns(i).entirecolumn.delete
end select
next i
end sub
Source: stackoverflow.com
Related Query
- For loop deleting rows doesn't work, need to run macro multiple times
- Why do I have to run my for each loop multiple times to delete rows in a table?
- Loop code to run macro multiple times
- Need help refining my excel macro for deleting blank rows or performing another action
- How to store singe dim in array for loop through multiple work book using VBA Macro
- EXCEL VBA Run Solver Using a Loop for Multiple Rows
- Excel VBA deleting rows in a for loop misses rows
- Run through a loop for more than 100,000 rows of data in two sheets in the same workbook
- Use VBA to loop through combo box list and run MS Access macro for each
- Excel vba macro copy rows multiple times based on a cell integer value
- Using a For Loop to run Macros on multiple sheets with ordered names like 1,2,3,4
- I need to copy multiple user selected rows to a different worksheet. The problem is, the selected rows are different each time the macro runs
- Copy Paste Macro suddenly doesnt work and shows error: That command cannot be used on multiple selections
- Need run that macro on multiple files
- How to run a vba macro for multiple excel files
- Need to run a Do While loop across multiple worksheets
- How can I run a macro multiple times per second in vba?
- Loop for a string with inbuilt macro with multiple validations
- Loop Text to Columns macro in Excel for multiple selected columns
- Run a macro multiple times down the sheet
- Optimizing a vba macro to work on multiple rows with one parameter
- Is there a way to write a macro that will loop to text join multiple rows of data in excel?
- 2nd For loop doesn't work until the 2nd time I run my Sub
- Run time error 13 on deleting multiple rows in Excel using VBA
- Why must loop to delete rows be run several times to remove all rows?
- How to measure query times for multiple queries run in a VBA Function in Microsoft Access?
- How to Sum Multiple Rows for same value in Excel using a macro with data manipulation
- Adding a Loop to Code Work for Multiple Sheets
- While using a For Each loop in VBA, the data gets duplicated multiple times
- Creating an Excel Solver Macro that loops objective, variable cell, and constraint for multiple rows
More Query from same tag
- VBA Macro Pivot Table Filter Error
- Loop to remove duplicates only works for the first iteration
- Open VBA window with macro
- Change ActiveCell to update a worksheet with data from various workbooks
- Why can't I reference another dictionary from inside a foreach loop? VBA
- imple game lags when i release a key and quickly press another key
- Find word with RegExp and bold
- How to display/remove row items which are 4 cells apart in listbox VBA
- Apply formatting to a range using array loop - VBA
- Automating import of an Excel file
- VBA Macro Selected Text Outlook 2010
- Use a date value in Excel to apply conditional formatting with VBA
- VBA macro in Excel to Run SQL Insert statement
- Concatenate multiple fields where some fields are empty
- VBA DoCmd.Open form on a group of records
- Open multiple Word docs with Excel and import data
- Use formula to update cells, but as values
- How to use array values that are dates as XValues on a chart ? (VBA)
- Update junction table
- Trying to copy a worksheet to an existing workbook in vba
- Data Type Mismatch in Criteria Expression in MS-Access
- Excel VBA Find Values through while loop, store in array, and pass to different sub
- What is an ideal way to get unique values into a multidimensional array in Excel VBA?
- Excel VBA: Update a cell based on conditions
- VBA to C# Value Statement
- Excel if function
- CopyPicture method of range class failed - sometimes
- Use columns.add(...) in Word with non-uniform column widths?
- VBA - import n lines from multiple text files
- name variable from oracle sql string value in c#