Posts by mamercio

Re: Autofilter range (table) field with multiple criteria 'contains'

Thanks a lot for the quick reply Luke. However, the reason i didnt want to used advanced filter was precisely the thing about having to store my filters in a hidden range/sheet just for this purpose, specially cause the macro is designed to be stored in my personal macro workbook and, thus, should be working regardless the workbook (and i think it is too much effort to add 1 sheet and then deleting it behind the scenes just to filter a table).

I'll try to find a workaround or just assume the impossibility! Thanks a lot again!

PS: how do i upvote ur reply? as it is deffinitely the only way to solve the pb...

Hi all,

I already designed a macro in which i create a vba array of filters for a field (something like arrayFilters=Array("apple", "banana", "tomato")) and then, with

TABLERANGE.AutoFilter Field:=filtercolumn, Criteria1:=arrayFilters, Operator:=xlFilterValues

i manage to filter the specified column by the exact values in the array.

However, what i want to achieve is the same only that instead of filtering by the values that match exactly each filter condition, doing by 'contains' condition (currently trying to do so by creating an array like .arrayFilters=Array("*apple*", "*banana*", "*tomato*")), but no luck so far.

I have read threades on advanced filtering, but for that i need to provide a range for criteriarange parameter, and i want all this process to go behind the scenes, so to speak (currently the user only has to write the filtering conditions on top of each header column separated by comas (such as: apple, banana, tomato) and then the macro should do the rest (i.e. loop through each column header and create an array of the filters to apply to that particular field).

Any light on this??

Thanks a lot in advance!

Hi there,

Im trying to find, in VBA, a value within a defined range (from now on SearchRange). The problem is that the width of the columns containing the SearchRange must be narrower than the width that would allow the values in the SearchRange to be displayed (I am only using the SearchRange as an accesory, and the row containing it is hidden).

The SearchRange consists of a unique row, containing dates from 1st of january to 31st of july (but its not relevant to the case that the values are dates as i also tried converting them to numbers and the result is the same).

The problem is that Find function doesn't work when the value is not displayed (which i find somewhat awkward and disturbing). I also tried with application.match function, but didn't manage to make it work (despite that, i know match works even when the SearchRange is hidden, as i do this in other workbooks, although is use it to get the row reference, which i later use to obtain the cell im looking for)

Can somebody help me findind the optimal way to pursuit this task? If not, i'd be forced to autofit columns, do the search, and set them back to their original width, or some other inefficient way... but the macro is already pretty complex and takes plenty of time, and im trying to optimisize it as much as possible.

Thanks in advance,

Alberto P.

Re: Show table headers row when scrolling down until end of table

Thanks for ur quick reply NBVC! I already considered that, but it is kind of a mess.. This excel document im working on its to be used by numerous users, and it has to be as simple as possible, working as similar to a web application as possible.. I was very surprised to learn there was no predefined feature in excel for this.. Seems like a pretty common and practical demanded-feature to me..

But thanks again!

Hi, i've been doing some research on the internet to check if this can be done with another feature different from freeze panes.

What i want to achieve is to show the table headers row only when user scrolls down enough for it to dissappear. Freeze panes would be useful if there was no data previous to the table headers, but proofs to be useless otherwise, cause if you still want to keep this info visible you must sacrifice most of the screen and let only a small area for scrolling down.

I want the worksheet to keep its normal appearance and only when i scroll down enough keep showing the top row with the headers.

I know this is possible with tables in the latest versions of excel, although it only shows the headers in a single line (without the wrap text feature), so if the header is "longer" than the width of the column, the string is cut to its width (and thus, not showing the entire header string).

Is there a way to achieve this? Let me know if you need further explanations, cause i am aware that its difficult to picture the idea just by reading its description (even more if the one giving the explanation is not a native english speaker, as myself).

Thanks in advance!

Regards,

Alberto P.

Re: Determine in VBA if range (table column) is empty

OK my bad!! i forgot to put "Range"!! now it works perfectly like this:

Sub test() 
    If WorksheetFunction.CountA(Range("Sheet 1[Column to be copied]")) = 0 Then 
        MsgBox "Range is empty!" 
    Else 
        MsgBox "Range is not empty!" 
    End If 
End Sub

Thank you very much! Regards!

Re: Determine in VBA if range (table column) is empty

Thanks patel, but its still not working for table defined ranges (at least not for mine). I tried it defining the range as you recommendated, and it works, but if i use the table range (more dinamic, cause the tables vary in number of rows from audit to audit) it always considers that the range is not empty, even if it is.

As i said, i modified it to this, but its still not working correctly (it runs the macro anyway):

Sub test() 
    If WorksheetFunction.CountA("Sheet 1[Column to be copied]") = 0 Then
        MsgBox "Range is empty!" 
    Else
        MsgBox "Range is not empty!" 
    End If
End Sub

Hi, I want the macro to check if a column in a defined table has values and, in this case, copy it and overwrite another column of the same table.

I have developed a solution, which is apparently correct (no errors appear) but runs the macro regardles if it has values or not.

Sub CopyPasteIfEmpty()
Sheets("Sheet 1").Activate
If Not IsEmpty("Sheet 1[Column to be copied]") Then
Range("Sheet 1[Column to be copied]").Copy[INDENT]Range("Sheet 1[Column to be overwritten]").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:?xlNone, SkipBlanks:=False, Transpose:=False
Range("Sheet 1[Column to be copied]".ClearContents[/INDENT]
End if

The thing is that this is part of a bigger macro which i run to clean the excel from the data of the last time i used it (to audit). So if the column "Column to be copied" has no values, the macro would overwrite the values of the "Column to be overwritten" column with no values (wich is annoying to say the least).

Thanks in advance, and excuse me if i made any mistakes, as i am new to this forum.