specialcells

  1. J

    VBA Macro - Select range of SpecialCells

    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...
  2. wsnyder

    Get All Visible Rows After AutoFilter

    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...
  3. D

    Copy Data between Workbooks

    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...
  4. N

    Trouble with Search,Select,Insert Macros - HELP!

    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...
  5. L

    VBA: Getting an array from a filtered range

    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...
  6. B

    SpecialCells VBscript to get all Formulas fails w/Unable to get the specialCells property of the Range Class

    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...
  7. S

    Visible range from UDF passed filtered Listobject columns as argument

    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...
  8. G

    Slow Macro with SpecialCells and looping

    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...
  9. P

    Probably a bug when using ListObject with Autofilter

    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...
  10. B

    Cond Fmt formula export works! But very slow-code alt to SpecialCells?

    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...
  11. J

    VBA Run time error when counting blank cells

    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...
  12. D

    Change Color of Character within Result of Formula

    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...
  13. P

    Is there a way find out on which category of specialcells the activecell falls into?

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

    Replace Selections with Activations in SpecialCells formulas filtering visible cells

    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...
  15. J

    VBA SpecialCells Method

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

    Comparing Filtered Columns

    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...
  17. I

    VBA - Help - Count visible cells between range [Autofilter/For/If/VisibleCells]

    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...
  18. H

    Problem when using SpecialCells

    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...
  19. J

    Using specific cell from filtered list

    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...
  20. N

    VBA code to only use Special Cells IF extra rows exist

    HI All, I've got a spreadsheet that via a Macro Button and some basic code I have written, will copy data into my spreadsheet table from another spreadsheet (via chdir ) . The data pasted in will have a few columns next to it, which have a series of formulas. My question is about the following...

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top