Below is the loop. An error occurs at Rows("y:y").Select
The y value that would be found in cell C500 would always match the row that would be selected and deleted. For example if cell C500 had the value 13, I would want Rows("13:13").select and then deleted.
Sub Macro2()
'
' Macro2 Macro
'
'
For y = 1 To 100
[code]......
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,
Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")
For Each c In rng
If Left(c.Value, 1) "~~" Then
c.EntireRow.Delete
End If
Next c
How do I loop a select case so that it ends at a specific cell.
View 14 Replies View RelatedI have a list of worksheets that I want to select from a list.
Region 1
Region 2
Region 3
etc....
These sheets are in a workbook that contains other non Region sheets.
How do I select all of them without hardcoding the sheet names?
I have a subroutine involving a Select Case and a loop.
I am of the opinion that writing the loop within the Select Case would be faster than having the Select Case within the loop.
Am I correct or does it not make a difference?
I am trying to loop through and read values from 4 combo boxes which I have renamed SaveOptionBox1 to 4. They are ActiveX controls. The code throws no errors but I can't output the values of my array 'dirname' on the sheet to prove it worked.
Code:
Sub Directory()
Dim ROOT_PATH As String
Dim cnt, dirnumber As Integer
[Code]....
I've put the following code together;
Sub RemoveStars()
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet1").Select
Cells.Replace What:=" *** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Sheets("Sheet2").Select
Cells.Replace What:=" *** ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Sheets("Summary").Select
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
There are more sheets that this is run on, but I am sure you get the idea. I guess that this can be done using a loop, I would prefer to code the sheet names in, there are 5 in total.
My issue is I want to use a for loop to go through a spreadsheet. Within the for loop if a certain condition is true I want it to select all columns from A to AW and merge them together.
Example of what I have so far:
Sub MergeHeaders
dim i as Intger, LastRow as Integer
LastRow = Cells(Rows.Count, "CP").End(xlUp).Row
For i = 1 to LastRow
'Check if the leading character is a letter, not number
If Asc(Cells(i, "AP")) > 64 Then
'I want select from A to AW only for current row i, then merge
end If
next i
end sub
I am learning about Case Select-
I want to loop through every cell in col. V the range of
i = 6 to LRow
and find any value that equals 13, if it equals 13 then the value in (i,"W") will be "True"
How do I select the empty cell (once located after using the below loop) so I can paste into it?
[Code] .......
I'm looking to create a macro that does the following:
- Loops through a folder structure
- Opens a specific subfolder within each folder based on name (i.e. "*Financials*")
- Opens a specific file within that subfolder "*Financials*" based on both most recently modified a naming criteria (i.e. "*Model*")
- Performs an action on that file (a macro created elsewhere that I assume I can call here)
- Closes that file, and moves on to the next file matching the criteria above
As a self-diagnosed VBA novice, I'm having trouble adapting code found in various forums for my specific project.
I've found examples that address aspects of my desired macro or do similar things, but I can't figure out how to put them together.
Open most recent file:
[Code] .....
Loop through folders and subfolders and print all files:
[Code] ....
I've never worked with Excel macros prior to today. I'm working on this for a friend.
I've got this sheet that's going to a web address, importing a table from the page, and making a line graph of the data. I got all that to work fine (after a lot of trial and error). Now I've got the data, and I want to make a scatter plot of it...
Here's the thing. I want each month of the data represented as a line on the plot.
This is hard to explain. It looks like this:
[url]
So from there, I want a loop to scroll through the dates and select a range that represents January, a range that represents February, etc, so I can save those ranges as variables and make a scatter plot with 12 curves on it.
Here is the code I have so far, in case you want to see it, not sure why you might need to though.
I have developed a user form in VBA which is used for entry into an excel worksheet. From this data, I need to do the following:
1) Create 2 output files for Job data entitled "J" and another file entitled "S". The "J" and "S" is captured in a field in the data. I need to hit a button that allows these 2 output files to be produced, so I need to 'loop' through rows of the data to do this/
2) In addition to 1), the user needs to be able to 'select' the date range they would like to see 'J' and 'S' data over. This will be based on a field called "Startdate'. How could I do this with the button above-can I parse an input value to a form and select the 'J' and 'S' files on this.
I have output from mainframe that is copy/paste to an excel workbook. I need to have VBA loop and delete the 15 rows. This is mainframe output so it will always be 15 rows
Count
Campus
Name
Course
Start Date
1
336001
Student1
1
9/2/2003
[Code] ............
Within my loop I would like to delete an entire row - but as my loop goes down each row one by one as soon as one row is deleted the next row moves up and is then not included in the loop.
Dim Rng As Range, Last As Integer, Dn As Range
Sheets("ttool").Select
Last = Sheets("old ttool").Range("J" & Rows.Count).End(xlUp).Row
Set Rng = Range(Range("J2"), Range("J" & Rows.Count).End(xlUp))
For Each Dn In Rng
If Dn "" Then
Last = Last + 1
Sheets("old ttool").Rows(Last).Value = Dn.EntireRow.Value
Dn.EntireRow.Delete
End If
Next Dn
If I run it 4 times it completes as it should. Or if I change it to entirerow.clearcontents all rows are cleared.
How do I make a loop to delete a row if the cell A is empty. Example: If cell A3 was empty I want it to delete the row. If cell A4 had data I would like it to skip that row and go on to cell A5.
View 6 Replies View RelatedI am using this to delete rows from my excel sheet. I basicaly start my script by placing xxx in cell B2000. I want to optimize my code and maybe some how tell excel do until last cell. I am playing with:
"Do Until lLastRow = Range("B" & Rows.Count).End(xlUp).Row"
But not shure how to procede with this.
Range("B1").Select
Do Until ActiveCell = "xxx"
If ActiveCell = "Ticket" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
I have two worksheet. One worksheet (ws1) contains a list of item I want. The other sheet (ws2) contains multiple columns where the header (row 6) is named by item name.
I have the following code which deletes the entire column if the header name is not in the list contained in ws1 :
VB:
Sub delete_col()
Dim wanted As Boolean
Set ws1 = Workbooks("test1").Sheets("aaa")
[Code]....
First of all, this loop does not work properly since deleting the entire column shift them on the left, so when I first analyze column 11, if I delete it and then analyze column 12, the real column 12 now became column 11 and so on...
Secondly, this code is pretty slow. I am pretty sure I don't have to loop through my initial item list everytime I do Instr on a new column.
I have this setup successfully for the deletion of columns and have modified the below to apply to rows, however, I am not seeing the results I expect (or actually anything).
Code:
Sub Analytic_RemoveNA()
'Remove NA
Dim j As Long
For j = 35 To 4 Step -1 'Rows 35 to 4
If Cells(9, j).Value = "NA" Then Rows(j).Delete
Next j
End Sub
I have a data set that spans from B4:I32. If column I has "NA" in any row within that dataset, I want to delete the row.
I use this code to delete some rows, how I would modify it to work in another spreadsheet where it would "Loop" through and delete rows that start with "User:Kellcyna" down to where the rolls start with "Numbers", and delete the rolls that start with "Total cost center" down to where the rolls start with "Numbers".
The data can contain up to 50000 rolls at times.
Sub Finally()
Application.ScreenUpdating = True
[a:a].AutoFilter Field:=1, Criteria1:="="
[a2:a65536].SpecialCells(xlVisible).EntireRow.Delete
If [a1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
[Code] ........
Here is a sample of the data I need the macro to work on. The rows I need deleted are the rows that are highlighted.
User: Kellcyna STANDARD HOURS BY COST CENTER Date: 09/29/2013 Time: 15:10:04 Page: 10
Comments:
Order Op Emp Post Work ctr Setup Unit Planned Earned Total Actual Actual Actual Total Total Orde C R
# Date SU Unit Plnd Stds Setup Run Tme Brd Tme Prod Run Time E
104527059 0010 00000000 09/25/2013 HSW01 0.000 0.4 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01
104523849 0010 00000000 09/25/2013 HSW01 0.000 0.2 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01
[Code] ...........
I'm trying to create a function that will search for a certain value and if that true, delete certain cells in the same row. So far, I've come with the following and not sure how to write particular line.
So this function will go through column G and count the rows since it could have 1 or 50,000. So for example if column G2 has a value anything other then "Pending" then I need cell T2:Z2 to be deleted.
I know this line is the source of error Rng("T & Rows.Count:Z & Rows.Count").Select but I don't know how to express it.
Sub Check_Status()
Dim iLastRow As Integer
Dim Rng As Range
iLastRow = Cells(Rows.Count, "G").End(xlUp).Row
For Each Rng In Range("G1:G" & iLastRow)
If Not Rng.Value = "Pending" Then
Rng("T & Rows.Count:Z & Rows.Count").Select
Selection.ClearContents
End If
Next
1. Delete all data after row 51.
2. Find the average cost (column 4) and delete all rows where the cost is below average. Thus only retaining rows with an above average cost.
3. Then I want to conditionally format any number in column 6 which appears more than three times.
4. I want to loop this through all worksheets in my workbook.
I'm trying to loop thru the worksheets(which are imported from a web form in Outlook) and delete row 31 until the text "PARTS" is reached.
I need to do this to format each sheet exactly for export to an Access database. The code below works when I step thru it but when I save and run from a button it gets stuck in an endless loop. Any help would be greatly appreciated.
JB
Sub DeleteRow31()
Dim w As Worksheet
Dim rng As Range
For Each w In Worksheets
w.Select
Do Until Cells(31, "B") = "PARTS:"
Range("B31").Select
Range("B31:K31").UnMerge
Range("B31").Select
If Not Cells(31, "B") = "PARTS:" Then
Selection.EntireRow.Delete
End If
Loop
Next w
End Sub
I have two worksehets. The first worksheet contains data on products. The product code is in column A. My second sheet contains a list of product codes in column a. I want to delete all rows in worksheet 1 where the product code doesnt exist in the list in worksheet 2. Can someone provide a outline for a macro to loop over sheet one and look up the value in worksheet 2 and delete the row from worksheet 1?
View 3 Replies View Relatedi have a set of data as below and wish to delete ENTIRE ROW if cell is the same but keep the first entry when code comes to it..ie in the data set below excel would keep first row and delete 2nd,3rd,5th, 6,7,8 and then go to next unique identifier which would be AU0000LIFHB3 this would not be deleted as it is unique the would proceed to AU300GPTC011. this process would then stop when no data was available
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU0000LIFHB3
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU300AUS1050
AU300GPTC011
AU300GPTC011
AU300GPTC011
AU300GPTC011
AU300INTE018
AU0000LIFHB3
0
AU300SUNQ027
I'm new to marco's and want to write one that select and cut a row with information and delect the blank row after it paste.
View 2 Replies View RelatedI have a report that contains several rows of data. This data is separated by page breaks, so for example, there could be 1500 rows, separated into 90 pages by page breaks. Each range may have anywhere between 35 and 45 rows, but some of those rows may contain no data.
I would like to loop through the cells column A, look for a certain criteria, then delete the range around that certain cell that is contained within the page breaks.
I have code that will loop through the cells in column A. I also managed to create code that will delete a set range but since the ranges aren't always a uniform number of rows I need another fix.
I am trying to run (or loop) through a column of cells, and if cell contains certain text (e.g. ALPHA, BRAVO, CHARLIE) and delete the row if found.
My macro as I run it:
Sub DATA_TEXT ()
'Number of cells to loop through (I am unsure how the .xlend works!)
For i = 1 to 2500
'If function defining the criteria.
If cells (1, i) = "ALPHA" OR "BRAVO" OR "CHARLIE" Then
[Code] .....