How To Select Range And Clear Based On LR Variable
I want to select and clear a part of a sheet.
here is what I have.
Sub clear_data()
sheets("sheet1").select
lr = activesheet.usedrange.rows.count
Here is where I get lost in translation (syntax).
I want to select starting at Bcolumn through bycolumn but the row be set with the LR from above, since the rows always change.
I could write B2:by2000, but i want to use the LR variable to define the number of rows i have.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
View Replies!
View Related
Clear Variable Range
why the first version below works but the second doesn't 1st ThisWorkbook.Worksheets("GBP%"). Range(Cells(8, c), Cells(z, c)).ClearContents 2nd Worksheets("GBP%").Range(Cells(8, c), Cells(z, c)).ClearContents c = 16 (uses a for statement to go through columns 16 to 111) z = in this case 24, but is dynamic depending on number of files in a list Currently using Windows 2000 with office 2000
View Replies!
View Related
Clear Set Range Variable
is it possible to clear the value in a range variable? For example say: Set A = Range("B4") Is there a way to clear the value of that range, in other words make it blank again? And unfortuanlly i can't just make it equal to another range i need it to be blank.
View Replies!
View Related
Clear Cell In Variable Dynamic Range
In my macro, NextRow is defined as the first empty row in a set of data (NextRow = Range("A1000").End(xlUp).Row + 1). How would I clear the contents of Columns A:H in NextRow in VBA, keeping in mind that NextRow is always different for each worksheet I'm working with?
View Replies!
View Related
Select A Variable Based On A Variable
On my userform I have 20 comment icons (imported pictures), that when clicked need to bring up an InputBox for the user to add a comment, and store that comment in a Public variable specific to that comment which will later be written to the spreadsheet. To keep it simple, lets say I have two comment icons to click, one to add comments to the "Testing Completed?" field, and one to add to the "Sign-Off?" field. The first comment icon is named TestCompIcon, the second is SignOffIcon, and the public variables they write to are called TestCompComment and SignOffComment respectively. To avoid having to code the InputBox procedure for every comment icon on the userform, I was hoping that upon click, the icon would call a centralized routine that would establish the name of the variable that needs to be written based on the name of the icon comment that was clicked. Something like as follows:...............
View Replies!
View Related
Variable Range Select - Then Clearcontent
This one should be easy but I keep running into the same wall. On every page I have single cell defined name range based on the sheet name & "_startcell" So on sheet MfgReq I have cell A3 defined as name range "MfgReq_startcell". I would like to the following: .clearcontent using the "MfgReq_startcell" as the upper most left cell, all the columns to the right, and all the rows down. on this sheet it might be 5 columns wide by 4000 row & on another sheet it might be 50 columns wide by 50000 rows.
View Replies!
View Related
Select Variable Print Range
I am trying to create a command button on my userform that will allow me to print all the records in the worksheet....in other words it should only print the rows with data...... *my worksheet is called "complaintData" - this is hidden and not active sheet. *I have columns A to J with information, with heading from A1:J1. *I want to be able to print all the rows with values in column A. (Not all the columns except "A", will have values for every record....some may be blank for some records... *I want all the columns to fit in in one page with headings (A1:J1) being first row on every new page..... *page setup should be landscape... I have tried a few codes from this forum, but not sure where I have goofed up....am still trying learn VBA.....would appreciate if anyone can fix this code for me....cheers Private Sub cmdPrint_Click() Sheets("ComplaintData").Visible = False Dim ws As Worksheet Set ws = Worksheets("complaintdata")
View Replies!
View Related
Select Range With Row Variable
I am doing is setting a variable called eof to the number of rows (with text) and i just want to select columns A1 - G1 and the eof range! Dim eof As Integer With Sheets("Coverage Count (%)") eof = .Range("G65536").End(xlUp).Row 'crashes on the .select .Range("A1:G1" & eof).Select Selection.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With I've done this before and I could have sworn this worked...but i must be missing something.
View Replies!
View Related
Select A Variable Range Macro
I have tried various syntaxes but nothing is working for me. I am using XL 2000 so it doesn't have the relative address function in the macro! So here is what I have ActiveCell.Range("A1").Select ActiveCell.R1C1 Range("R1C1:R1C85").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues Application. ScreenUpdating = True Range("A1").Select I need to be able to select from the originating cell which will always be in "T" Col thru to "CZ" col but will be on different rows.
View Replies!
View Related
Select Variable Data Range For Graph
I need to select data from columns A, B and I for the graph. I have the below code but get an error message. Can anybody help ? Set cellGR1 = Cells(2, 1) Set cellGR2 = Cells(K, 2) Set cellGR3 = Cells(2, 10) Set cellGR4 = Cells(K, 10) Charts.Add ....
View Replies!
View Related
Select/process Variable Range With Macro
I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses. Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good. Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem. I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails. Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records. Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.
View Replies!
View Related
Clear Contents Of Range Based Upon Values
There is data(numerical) or blanks in each of the cells in the range H32:O37 I want to clear the contents if they are a duplicate of the previous line. Looking for a macro hopefully. Found many to delete the whole row but just the specific range based upon the duplicate criteria..As there is data surronding that area do not want to delete whole row just clear that specifc area. H32:O32 clear contents if same as H31:O31 if any valuse are different then stays the same H33:O33 clear contents if same as H32:O32 if any valuse are different then stays the same H34:O34 clear contents if same as H33:O33 if any valuse are different then stays the same H35:O35 clear contents if same as H34:O34 if any valuse are different then stays the same H36:O36 clear contents if same as H35:O35 if any valuse are different then stays the same H37:O37 clear contents if same as H36:O36 if any valuse are different then stays the same
View Replies!
View Related
Conditional Clear Based On Other Worksheet Range
I have a range on worksheet "CONTROL" from S129:S228. There are 100 rows there. I have another range on the active sheet from B17:N116, also 100 rows. If S129 has a value of 1, then I need row 17 on the active sheet to be cleared (columns B-N). And so on all the way to the bottom of the ranges. If it has no value in the cell, then no clearing takes place. So it needs to test the whole range at once. I really have no clue what the variants and ranges should be, and have NO CLUE how to define a variable range so that I can use the value that R is on in the testing cycle to tell the active sheet which row to delete.
View Replies!
View Related
Clear Contents Based Upon Value In Row Range
I'm trying clear the contents of a column range when the cell above this column range is empty. I know how to do this for one cell, but I would like it to work for a rangefrom R15:BB15. If IsEmpty(Range("r15")) Then Range("r16:r35").Select Selection.ClearContents End If End Sub
View Replies!
View Related
Copy Or Clear A Range Based On Checkbox
I am good at excel but an infant with VBA. I have inserted a checkbox onto my worksheet. I want it, when checked, to select a row of data ("k5:k16") from Sheet 1 and copy it to ("a1:a12") in Sheet 5. When the checkbox is unselected, I want the ("a1:a12") to be cleared. I've tried several different ways to copy and continue to run into errors. I know there is a simple solution, but it's beyond me.
View Replies!
View Related
Automatically Clear Range Based On Cell Criteria
I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range rng = Range("F" & Target.Row & ":M" & Target.Row) If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then If Target.Cells.Count = 1 Then If Target.Value = "Yes" Then Application.EnableEvents = False rng.ClearContents Application.EnableEvents = True End If End If End If End Sub
View Replies!
View Related
Select Range Based On A Cell Value
I have a worksheet with a block of data. The size of this block changes. I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet. So far I have everything working fine. Now I want to create a button to trigger a macro to sort that block. I can figure out the button and make it trigger a macro. One of the lines in the macro is
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Select Dynamic Range Based On Non-empty Cells
I require code to identify the last row in column 'A' that contains data, and then to select every row up to that one, and each column up to 'H'. My data begins on row 3, and the rows with data varies from row 7 through 120. The columns with data is constant so there is no need to test in that direction.
View Replies!
View Related
Sum Based On Multiple Conditions & Variable Range
I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached. This has been driving me nuts for a few weeks now.
View Replies!
View Related
If Select Sheet, Select Range Statement
I have a command button on sheet MASTER. When the workbook is Activated I want it to check and see if in sheet COSTM, cell B3 there are the words "Project Number", if so then show command button (ClearPrevious), if not, don't show. Also, when the If statement is finished, then the workbook needs to end up showing the sheet MASTER. I have tried various codes and none work, or they are on perpetual loops. I know this has got to be simple, but cannot find an example to take from to solve the issue. Would appreciate any help offered. Below is code I have right now. Private Sub Worksheet_Activate() If Sheets("COSTM").Select Range("B3").Select = "Project Name:" Then Me.ClearPrevious.Visible = True Else Me.ClearPrevious.Visible = False End If Sheets("MASTER").Select End Sub
View Replies!
View Related
Select Rows Using A Variable
I am trying to select rows that contain text using a variable counting number of rows so rNumb = count number of rows The Problem Worksheets("Table").Rows("2:rNumb").Copy rNumb won't work. What is the proper syntax?
View Replies!
View Related
String Variable To Call Defined Range Variable
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet? I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View Replies!
View Related
Set Range Variable Using Variable Row Number
I am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it. Dim Totals As Range Set Totals = [U37: AE37] Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
View Replies!
View Related
Select Entire Row With A Named Variable
I have this problem quite often and always have to think of a way around it, however I think there must be a simple method of doing what I want. If I want to select row three, I would write: Rows("3:3").Select However, if I have a variable which holds my row number how do I rewrite the above code? I've tried serveral varations of: Rows("CurrentRow:CurrentRow").Select
View Replies!
View Related
Variable Appended To Combobox Name In Select Case
I have a userform with 3 Comboboxes, all of which need to be populated from the dropdown option before OK is pressed. I haven't used any fancy names as this is just a straightforward (or so I thought) entry form ... the UserForm is called UserForm1 and the 3 ComboBoxes are called ComboBox1, ComboBox2 & ComboBox3. I have tried to be economical with my validation routine but I cannot get an incremental variable added to the end of the word "ComboBox" to be recognised ... my code is below : Private Sub Validate_ComboBoxes() While MyComboCount < 3 MyComboCount = MyComboCount + 1 MyComboBox = "ComboBox" & MyComboCount Select Case "UserForm1." & MyComboBox <<<< This fails Select Case UserForm1.ComboBox1 <<<< This works Case "" MyValidCheck = MyValidCheck + 1 Select Case MyComboCount Case "1" MsgBox "Enter the number of weeks for this period" Case "2" MsgBox "You need to enter a start date" Case "3" MsgBox "You need to enter an end date" End Select End Select If MyValidCheck > 0 Then MyComboCount = 4 End If Wend End Sub
View Replies!
View Related
Select Rows With Variable Last Row Number
I'm trying to write a macro that will validate data in variable amount of rows but will always be in a specific column. what i'd like to do is count the amount of row entries in Column A to give me "iRows." Then validate data in column E from E1-E"iRows." Currently my data validation runs for the entire column which means i can not validate blank entries. Once I can limit the validation process from E1 to E "iRows" then I can consider blank fields and mark them as invalid. Columns("E:E").Select With Selection.Validation .Delete .Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _ :=xlGreater, Formula1:="1" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "Award Amount" .ErrorTitle = "Award Error" .InputMessage = _ "Please enter the current expected total value or current award amount for this contract." .ErrorMessage = _ "Award amount may not be set to 0.00. If you do not have an amount awarded simply make the award amount equal to the paid amount." .ShowInput = True .ShowError = True End With I've tried using different ways of counting the rows and have been able to define "iRows" the problem is defining the range to only column E from E1 to E"irows."
View Replies!
View Related
Clear Row Based On Checkbox
I have a worksheet with several rows (A3:H70). Column J contains checkboxes (1 in each row). I'm trying to develop a macro that will clear rows based on the value of the check boxes. For example, if the checkbox on J3 is checked, that row (A3:H3) will be cleared. If the checkbox on J4 is UNchecked, then that row will be skipped and so on.
View Replies!
View Related
Clear Contents Based On Criteria
I need some code that will clear the contents of a Row Range(C:T) if either of the contents of cells X and Y = "E" The code needs to look at cells X17 and Y17 and continue looking until it gets to X216 and Y216 So - If X = E but Y does not, then the row range C:T must be cleared. If Y = E but X does not, then the row range C:T must be cleared If both X and Y = E, then the row range C:T must be cleared If the letter E does not exist in either X or Y, then the row must be left alone
View Replies!
View Related
Prompt To Select File/path And Store As Variable
I am looking for macro that when run, will open a file explorer window and prompt the user to select a folder and file where they have data stored. Then I need it to be stored as a variable and used as a part of a "Workbook.Open Filename" command. The reason for this is that, I have a huge formatting marco stored within a workbook. When a user extracts a report from SAP, I want the workbook to grab the file that is extracted, open it and import all of the data in order to be formatted.
View Replies!
View Related
User To Select Variable Folder For Saving Files
I would like my macro to prompt the user to select a folder for files to be either opened from or saved to during the running of my macro. In fact, I need them to select two folders, one for this month and the other for last month. I think I need to set the two folders as a variable but being fairly new to vba I'm not quite sure how to do this.
View Replies!
View Related
Clear Used Range
I have a table on a worksheet and have for quite some time used a very successful macro UsedRangeClear. I found here on the forum. Anyway the macro still works on all tables on all other sheets except this one. This is the reason, somehow someone, maybe even me, copied something down or formated down to the 1,048,152th row of the worksheet. My workbook has grown from 5000 kb to 11,000 kb. I have tried to highlight and deleted the rows, highlight and clear contents and the following macros: Sub UsedRangeClear() On Error Resume Next ActiveSheet.Unprotect Password:="myfadra" Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.UsedRange ActiveSheet.Protect Password:="myfadra" End Sub Sub ReSetTable_UsedRange() 'For ICFMR Dim LR As Long LR = Range("A" & Rows.Count).End(xlUp).row On Error Resume Next ActiveSheet.Unprotect Password:="myfadra" Application.ScreenUpdating = False Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete ActiveSheet.ListObjects("Table7").Resize Range("A1:T" & LR) ActiveSheet.Protect Password:="myfadra" Application.ScreenUpdating = True End Sub Each option freezes Excel. I am sure there is something easy I am just missing. Soooo, Please offer any suggestions you can think of. A Loyal Mr. Excel Follower Alecia
View Replies!
View Related
Clear Cell Contents Based On Defined Name
I'm trying to clear cell contents based on a defined name given to a set of various cells in my worksheet. The cells are not continuous, but since they're given that defined name, I don't think it matters. The defined name is listed as "CommentsFields". The worksheet name is listed as "QPRForm_V6" I found a similar thread, but cannot get it to work with what I'm looking for. I'm relatively new to writing VBA/macros, so I'm not sure what to do.
View Replies!
View Related
Clear Contents Based On A Selection In Another Cell.
If I have a pick list, or drop-down list, in cell B21 and one of the options in that list is "Clear", how can I get the contents of cell C21 to be cleared when I select the "Clear" option in B21? Constraint... Don't want to use VBA. I am thinking of hiding a formula somewhere other than in C21 that evaluates B21 and does an If Then kind of deal to clear C21.
View Replies!
View Related
Clear Range From All Worksheets
I have a macro to clear all contents for a sheet in a workbook. The problem is that every file has over 20 sheets of data. Is there a way I can code a for statement to clear all sheets in this one workbook? Is it possible to make a for statement for multiple workbooks with several sheets? Range("A11:Z11").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.ClearContents Selection.Interior.ColorIndex = xlNone Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone Selection.Borders(xlEdgeBottom).LineStyle = xlNone Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Selection.Borders(xlInsideHorizontal).LineStyle = xlNone Selection.ClearComments
View Replies!
View Related
Clear Autofilter Range
I'm wrinting a macro to copy specific data from a table. To do that I'm using a Autofilter and a list of criteria. The macros works fine for existent data, I'm mean, when the result of the filter is not null, but when the filter doesn't find a result, instead of clearing the filter range, it keeps the last valid. The result is a colapse and freeze the Excel. I'd like to know how can I reset or clear the filter range? The code I'm using is: Sub Filter_01() 'Definições preliminares Dim rng As Range Dim rng2 As Range Dim ARLE Dim filter_valid, filter_invalid As Variant filter_valid = Array() filter_invalid = Array() 'Criação do Arquivo de Destino Caminho = "D:Documents and Settingscjcs.ABMeus documentosAutomaSIPPPlanilhas" Nome_Arquivo = "Produtos_Tanques.xls" Nome_Completo = Caminho & "" & Nome_Arquivo ...
View Replies!
View Related
Clear Range On Another Sheet
I have used the VBA and it works to select all data but I have an issue when there is no data to select beneath the column header. I am running this on multiple worksheets which vary with data week to week. Sheets("sheet1"). Range("A6:G" & Range("G" & Rows.Count).End(xlUp).Row).Offset(0, 0).Select selection.ClearContents How can I run this and not have it delete the column header when there is no data? Is there a way to change the formula so that it selects all cell columns of data without defining it?
View Replies!
View Related
|
TRACKER
