specialcells

  1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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?
  12. 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...
  13. 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?
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. S

    Special Cell question

    hi all, i am a noob to VBA with a relatively low exposure to programming, i am trying to achieve the below objective but getting error message Objective: select last non blank row and offset to next cell type a message in it error message: Runtime error 1004 : method 'Range' of Object...
  20. S

    xlcelltypeAllValidation not working

    so a bit of background as to what im trying to acheive here: I want a peice of code that runs through all cells & finds every cell with a drop down in. Now the below seems to work but on occassions it doesnt show some cells that have validation(drop down) in them...

Some videos you may like

This Week's Hot Topics

Top