overwriting problems while sheets are filtered.

Question

I use a code that finds the next available cell in the sheet, and insert there the detalis from the userForm Textboxes.

Nx = cells(rows.count, 1).end(xlup).row + 1

cells(Nx, 1) = TextBox2

The problem is that when the sheet is filtered, the values are inserted in 1 row after the last visible cell (not available). so sometimes it overwrites data.

how can i find the next available cell   (and not the next visible)

Answer 1

i tried it, It doesn't overwrite, but instead of finding the next available row  he finds a row much much below..     the next row is 1850    and the data was entered in row 4500.

I have tried to emulate this but for me it always selects the next row correctly (ie. after the bottom of the AutoFilter range data). I have tried the following but it always works for me.

1. Selected the entire columns before applying AutoFilter.
2. Selected additional rows below the data to be filtered before applying AutoFilter.
3. Deleted some rows after applying AutoFilter.

I am wondering if you have blank cells in the particular column and you have data in other columns below those blank cells.

It may be possible to overcome the problem with a little more coding like the following.

1. Save the filter criteria for all columns.
2. Remove the filtering.
3. Find the next blank cell as per your original code.
4. Paste the data to the cell.
5. Re-apply the filtering with the saved criteria.

However, to do the above I would need to know a little more about the filtering.

I can identify all of the filtering but dates can be a problem.

I can identify listed date filters but I cannot re-apply listed date filters. (ie. when multiple dates are selected in the filters). So if these are present then I cannot code it for you.

Single date filters are OK but I need to know what date format you use (ie. d/m/y of m/d/y)

It would help if you can upload a copy of the workbook to OneDrive and indicate where I will find the code you are using.  (Tell me the Module name and line number. Position the cursor at the position of the code and the Line number of cursor position is displayed on the tool bar like "Ln 43, Col 1".

Guidelines to upload a workbook on OneDrive:

1. Zip your workbooks. Do not just save to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder.)
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Do NOT fill in the form; Select "Get a Link" on the left side.
10. Click the button "Create a Link"
11. Click in the box where the link is created and it will highlight.
12. Copy the link and paste into your reply on this forum.

Answer 2

i tried it, It doesn't overwrite, but instead of finding the next available row  he finds a row much much below..     the next row is 1850    and the data was entered in row 4500.

Answer 3

The following example returns the next row after the bottom of the AutoFiltered range.

Explanation of code:

ActiveSheet.AutoFilter.Range is the generic reference of the filtered data. It includes column headers plus all rows of both visible and non visible rows. Therefore the next row after the last row will always be the empty row below the AutoFilter range.

Note the leading dot with  .Cells and  .Rows that ties them to the With statement.

    Dim Nx As Long

    With ActiveSheet.AutoFilter.Range

        Nx = .Cells(.Rows.Count, 1).Row + 1

    End With 

Without using the With/End With statements the line of code would be like the following.

Nx = ActiveSheet.AutoFilter.Range.Cells(ActiveSheet.AutoFilter.Range.Rows.Count, 1).Row + 1