AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS
I RECORDED THIS MACRO BUT I WILL LIKE TO MAKE TO AUTO SELECT THE ACTIVE CELL RANGE IS VARIABLE ON MY REPORTS MY CODE
Sub FORMAT_AS_A_TABLE()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$1900"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleDark5"
End Sub
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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 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
Apply Auto Filter To Active Range
I find it to be a little irritating that I have to remove the autofilter, then reapply it whenever I start entering data into a new column. I want a macro that I can assign to a button so all I need to do is hightlight the range of cells I want to apply the autofilter to, and click the button.
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
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 Replies!
View Related
Insert Picture & Auto Size To Active Cell Dimmensions
What I am trying to do is to give the user the ability to insert a photo and have it autosize into a range of cells--so far I can only get it to work with a single cell and a predefined file. Can this be refined so that rng references a group of cells and pic somehow lets the user input a file name or pick its name and location? Sub test() On Error Resume Next Set pic = ActiveSheet.Pictures.Insert("C: ange.gif") On Error Goto 0 If Not pic Is Nothing Then 'Found it!' Set rng = ActiveCell With pic .Height = rng.Height .Width = rng.Width .Left = rng.Left .Top = rng.Top End With End If End Sub
View Replies!
View Related
Auto Select Cell Value
I have 3 columns, A1, A2 and A3 I would like A3 to show me the value of A1 unless there is a value in A2 then it would show the value of A2 instead. I would also like to change the text color to reflect which cell the value comes from.
View Replies!
View Related
Auto Select Next Cell
I have a macro that unhides the next row when I enter someting into the row above. I need help with my code to get it to automatically select the cell in column C of the newly unhidden row. I will need this to happen each time a new row is unhidden (up to 50 times). Private Sub Worksheet_Calculate() Dim rng As Range Set rng = Range("A24") If rng.Value "" Then Application.EnableEvents = False Rows("25:75").EntireRow.Hidden = True Rows("25:" & rng.Value + 24).Hidden = False Application.EnableEvents = True End If End Sub
View Replies!
View Related
Set Variable To Row & Active Cell Column
I have a sort procedure I have been working on. Sort By Active Cell Column Now I would like to make sure the row of the activecell.column is row 7. I tried Private Sub comp_myMonthlyReport_SortAscend() Dim rng As Range With ActiveWindow rng = .ActiveCell(7, .ActiveCell.Column) End With Selection.Sort Key1:=rng, _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub But I receive this error: Run-time error '91': Object variable or With block variable not set
View Replies!
View Related
Auto Generate A Comment Box Based On A Cell Value That Has Variable Reference Cells
I have been given the following code and it works great. I now need to adapt it to the following scenario: In the attached sheet, the user has to select either, "Suburban" or "Squad" in row 5. If the user selects, "Suburban" I need this script to compare the values they enter in a given row to the value in column "B". If the user selects, "Squad" I need this script to compare the values they enter in a given row to the value in column "C". Look at row 48, for example. If the user enters, "Suburban" in cell D5 then the value they enter in cell D48 should equal "1". If it does not equal "1" then it should proceed with the adding of a comment. Conversely, if the user enters, "Squad" in cell D5 then the value the enter in cell D48 should equal "2". If it does not equal "2" then it should proceed with the adding of a comment.
View Replies!
View Related
Expand Active Cell To A Given Range
When i click commandbutton1: Copy Active cell (columnB), next cell (ColumnC), next cell (ColumnI), next cell (ColumnO) all of which exist in the same row. I will then paste the values into another sheet in the next available row which i think i have mastered. the cells will be pasted next to each other i.e. instead of column B,C,I,O they will be pasted as column A,B,C,D. My dilema is i am not sure if you can use multiple cell offsets to perform this or if it is better to use a loop.
View Replies!
View Related
Sum Range From Active Cell To Last Used
I Have searched existing threads but cannot find the answer to my query, As part of a longer macro, I want to sum all cells in a column which contains data. Each day the number of cells that contain data will vary. I have tired specifying all the cells in the column which contain data as a range, by selecting the top cell and using the Range(Selection, Selection, Selection.End(x1Down)) But I cannot get a SUM Formula of this range. If someone could show me the correct syntax to get a sum from a range like this I would be very grateful.
View Replies!
View Related
Range In Same Row As Active Cell
I'm looking for a piece of code, which would activate a certain Range i.e. the start of which would be in column A and the End in Column G. My problem is that the activated range of cells shuld be exactly in the same row as the currently active cell i.e. active cell B3 -> activated range A3:G3 .
View Replies!
View Related
Expand Active Cell To New Range
I have some working code that makes a number of changes to an excel report using VBA. Each time I execute the code I am taken to a different point in the excel report (depending on user inputs). What I now need to do is to change the cell selection from a single cell to three cells. For example, my code may result in cell B100 being the active cell. What I now want to do is change the selection from B100 to the range B100:B103. I would have thought I could write something simple like: Sheets("Group").Range("ActiveCell:Offset(0, 2)").Select I have tried every variation of the above but I can't seem to make it run. Where am I going wrong?
View Replies!
View Related
Copy Active Cell Down Adjacent Range To Right
Split from: Clear Range To Right Of Active Cell Down. I was working through this the first step was to clear the contents of the cells. After they are cleared, I have another sub that then goes and retreives a unquie number and places it back at the top of the column that I just cleared. Would this same code that cleared the cells contents work to copy the contents of the active cell down that same column, until the cell to its right is blank? -R-
View Replies!
View Related
Merge Range Based On Active Cell Row
I am working on a macros that creates a new row for every data entry. Below is the macros that I have. In the new row, I want for the cells in columns F through O to merge right after creating the row. How do I go about this? If Sigma = 0 Then Selection.EntireRow.Insert ' New row for new entries ActiveCell.Value = "NONE" ActiveCell.Offset(1, 0).Select End If
View Replies!
View Related
Range X Rows & X Columns From Active Cell
Im writing a macro and have a cell activated (using ActiveCell). If I want to select this cell (the activated one - i.e., k3) along with the 2 cells next to it (i.e., l3, and m3), how would i go about doing that? I'm wanting to shift 3 cells downward and I know how to shift them down, but dont know how to select the cells i want.
View Replies!
View Related
Select Previous Active Window
I have multiple excel workbooks open at the same time. I need to run a macro on one of them every 15 min but I need to return to the active window when the macro is done. How do I read what window is active then return to it when the macro is done? Sub AutoSave() dTime = Now + TimeValue("00:15:00") Application .OnTime dTime, "AutoSave" Windows("data.xlsm").Activate Sheets("Data").Copy Application.DisplayAlerts = False ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV ActiveWorkbook.Close Application.DisplayAlerts = True Windows(1).ActivatePrevious End Sub
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
Range Copy: Differ Depending On The Active Cell That's Selected
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this. The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got 'FindRemove = lstRemove.Value 'If FindRemove = "" Then End ' Goes to the start of the Data column 'Sheets("Staff").Select 'Range("B4").Select ' Tests current cell against FindRemove 'Do 'If ActiveCell.Value = FindRemove Then 'Call CopyPerson 'Exit Sub 'Else: ActiveCell.Offset(1, 0).Select 'End If 'Loop Until ActiveCell.Value = "" 'End Sub
View Replies!
View Related
Select Cell Within A Range
how to select a cell in opposite (i.e right side of a range) while using a macro. My macro selects a certain range based on user input. The active cell is the left most upper cell. I need to move the active cell within the range to the right upper most cell. I tried the short cut key - tab, while recording a macro, but the macro just selcted a that certain cell.
View Replies!
View Related
Auto Fill From Cell Range
I have a spreadsheet that is downloaded from an accounting system and requires extensive editing to be normalized before it is combined with data in another database. To normalize the data in a simple manner, I use the following macro: Sub All() Rows("1:37").Select Selection.Delete Shift:=xlUp Sheets("DL").Select Columns("A:D").Select Selection.Insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "Check" Range("B1").Select ActiveCell.FormulaR1C1 = "Benefitor"........................ It works great, however I would like to not limit my cell ranges for the auto fills. I would like to auto fill to the end of the data, which changes with every download (additional rows).
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
Intersect In VBA: Select More Than One Cell In The Range
I have a piece of code that changes the format of a cell using an intersect statement on the range in question. However, if I try and select more than one cell in the range then an error message is returned: 'Run Time Error 13': Type mismatch. I know that it is caused by selectin multiple cells - is there a way to error handle this so that, when multiple cells are selected, it ignores the intersect statement?
View Replies!
View Related
|
TRACKER
