specialcells

Hello! I have created the following code: Sub Filter_multi() Dim lastCol As Variant Dim lastRow As Variant Dim cel As Variant Dim test As Range Set ItemRange = Application.InputBox("Select range of the Item/Component Column. Starting on row 2 of that column, drag it down to the last cell"...

Hello, I'm doing a VBA macro button for automatizing some filtering and copying of new data from an Excel book/query (wb1) to another book refering to a client (wb2) with the latter's format. More specifically, I look for the last value in column 3 of wb2 and save it (I name it 'prt'), I...

Hi all, Using Excel 365. How do I get all visible rows count after autofilter so I can loop through visible values to pass to another sub.? I tired to get a new Range of visible rows which appears to give the correct non-contiguous address, but not the correct count of visible rows Thanks, -w...

Hello, I've been trying to get this to work: I have one workbook which contains new data every week. When I run the macro I want it to open that workbook and copy data from a set range into the workbook that contains the executed macro. The set range to copy is from one cell (top left) to the...

Hi Everyone, As you can tell by my username, I am very new to Excel VBA. I just started really utilizing it when I first realized that I can record a macro; however, as I continued it, realized what a powerful tool it can be for my work place when utilizing various data sheets. So far, many of...

Hi everyone, this i smy first post on this site, I hope someone will be able to help: I'm trying to create a function to extract data from a filtered sheet, without having to loop through all rows. I hoped the .SpecialCells(xlCellTypeVisible) would do the job, but it seems only to return the...

Trying to put a vbScript together that will go through an Excel Sheet and return all the Forumlas Found in the activeSheet. I am attempting to use SpecialCells to return the data. However, I get an error "Unable to get the SpecialCells property of the Range Class". Am I missing something to...

hi, i am filtering a listobject and passing some of its columns to a UDF called from a cell. the function is supposed to return only the filtered visible cells from those listcolumns. However it returns the whole range. i have posted the details here: excel - selecting...

Hi all, I am trying to run a macro to delete all the "..." in my excel document. I have tried to do this two ways, both by looping, and with special cells. Depending on the data, this is taking upwards of an hour. Does anyone have any reccommendations on how I can improve this. The data, does...

Hi, I created a workbook which has 2 worksheets. Sheet1 contains a Students ExcelTable. Sheet2 contains a Teachers ExcelTable. I am trying to filter records in the Students Table using the following macro. When running the macro with Sheet1 active, the filtered range, rng2 is $A$1:$B$2 as...

i am so excited that the code below works to export my conditional formatting formulas from huge spreadsheet! But it is super slow - it's looking through about 2000 cells and returning almost 200 formulas . . . but it takes almost 2 hours to run . . . I found this link about SpecialCells being...

Dim tbl As Range Set tbl = ws.Range("Table1") Dim archive As Worksheet Set archive = ThisWorkbook.Sheets("Sheet1") Dim rng As Range Set rng = tbl.SpecialCells(xlCellTypeBlanks)If rng.Cells.Count <> tbl.Cells.Count Then 'do stuff End If I'm not sure why, but I...

Hi everyone, I would like to change the color of all underscores ("_") that are in the output of a formula. Right now I have the code for changing the color in constant cells but can't figure it out for cells that have formulas. Any advice would be much appreciated. Dim rng As Range...

If not for activecell, for any other cell may be... Possible? Like cell.type?

Folks, How do I replace all selections with activations in this code: 'Delete Filtered Rows (how to remove selection?) Range("a2", Selection.SpecialCells(xlCellTypeLastCell)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.EntireRow.Delete I tried skipping...

Is it possible to return a range containing a "specific text"? Example: Return all cells containing *Pick this cell* in it?

Hey all-been working at this for a couple days and could use some input. What I'm attempting to accomplish with this code is to compare two sets of data on separate sheets(same workbook) and populate a third sheet with exceptions. I'm looking to have a list of what's missing in both of the...

Hi, Pretty much I'm attempting to count visible cells (after an autofilter) which exist between two amounts to find the total count for that region. Sec6Tot = the count Sec6Test = row for visible data CurrentSett(9,1) = Lower limit CurrentSett(10,1) = Upper Limit This line is currently the...

Hi, I have been attempting to use SpecialCells in VBA to extract all the numbers in a column (of formulas) and 'paste' in another sheet. The problem I'm having is the SpecialCells argument is only reading in the first instance where there is a number. Code: Option Explicit Sub...

Hi I am trying to working with a table where I want a Macro to i 1. Filter a table on column 2 for all entries for "Yes", 2. Set a range object for the visible cells within column 1. 3. Allow for a specific cell to be amended.within those visible cells I have the following code that works...