Sorting, Paste values VBA code

Hi,

I got stuck while recording a macro where cell ranges are recorded which are fixed. But while working, data range changes which macro to be taken care.

An excel file attached with necessary details.

Pl. help.

Thanks,
Nagesh.

Hi mehmetcik,

While executing below error msg displayed.

Run-time error '9':
Subscript out of range

When i click Debug, below message highlighted in yellow:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=11, Criteria1:="<>"

Pl. help.

Nagesh.

Hi mehmetcik,

Pl. ignore the above post.

Will check thoroughly and will come back.

Thanks for your time and efforts.

Nagesh.

Hi mehmetcik,

Run-time error '438':

Object doesn't support this property or method

'Select visible cells
Range("K2:K" & lr).Selection.SpecialCells(xlCellTypeVisible).Select

Pl. look in to it.

Thanks,
Nagesh.

First of all Thank you mehmetcik & sintek for your time and efforts.

Sintek, your code looks small & simple.

Hope the numbers in the brackets are column numbers (My actual file have 100+ columns and i have to understand the concept first to incorporate in my file)


Request you to pl. include code as per below:

1. Sorting sequence should be Column 11 first and then Column 1,2 & 3 together (In the sample file only 1 column shown but in my original file three columns i.e. Col. 1,2 & 3 together to be sorted. Pl. provide code for sorting three level sorting)
2. Col. B Formulas to be replaced with Paste values (only to the extent of "CLOSED" rows) (Excluding the Header Row)
3. Col. E:J Data to be deleted (only to the extent of "CLOSED" rows) (Excluding the Header Row)
4. After the executing your code, filters are vanished, but filters are to be kept in tact.

Pl. help.

Thanks,
Nagesh.

Hi Sintek,

Thanks for the support and interest.

An excel file attached with necessary details.

Pl. help.

Thanks,
Nagesh.

Hi Sintek,

It is working wonderfully. But noticed a small issue. When adding data in new row, all the formulas are copied to newly added row except where the data is deleted (Red colour columns).

Pl. look in to it.

Thanks,
Nagesh.

Since it is Table, when i put data in to new row (in first column), all the formulas are automatically getting copied as per the last row. Even yellow highlighted columns' formulas are copied, which as per the code, become paste values but formulas are copied to new row. Only Red columns (Where the contents are cleared) for which formulas are not copied.

Pl. do something.

Sorry if am not able to explain my requirement.

I checked each and every step manually and noticed that if the code exclude last row while deleting the contents (Clear contents), the issue is resolved (the formulas are copied to new row).

Pl. incorporate the change in the code. (since i have zero knowledge in the coding).

Pl. help.

Thanks,
Nagesh.

OK but This logic should be applicable only for "CLEAR CONTENTS".
For "PASTE VALUES", it should be as it is (-1).

Will the code work if I Copy and paste [With .Offset(1).Resize(.Rows.Count - 1)] after [Next Area]?

If not pl. advise how to incorporate the change.

This Gem Sintek.

Thank you very much for your time and effort.

Nagesh.