Posts by flo_san

  • VBA auto numbered according to hidden rows and skip blank adjacent cells

    Hello,


    Thanks for noticing me about code tags and for your prompt answer.

    It seems to be a better code bat, unfortunately, it didn't work how I wanted to. Maybe I haven't made myself clear.

    Actually, I've just noticed that I made a mistake with the following sentence which is not appropriate:

    "In column "B" I wrote (typed) the serial numbers, skipping the cells that have adjacent (in column C) blank ones."

    Sorry for my bad english. Well, let me rephrase:

    "In column B I typed the serial numbers, skipping the cells that have adjacent ones (in column C) with titles."


    I'll try another example in the attached xls file.

    In the left side there is a part of my table; in the right, I tried to explain how I want to look when the user selects the category of queries. So, the titles will appear in the table when category selected (and inappropiate rows will be automatically hidden) but do not have serial numbers. Only the questions will have.


    Thank you again,

    Files

    • example.xlsx

      (9.76 kB, downloaded 12 times, last: )

  • VBA auto numbered according to hidden rows and skip blank adjacent cells

    Hello everyone,


    I am new on this forum and, also, on VBA coding.

    I'd like to ask you a little help in order to have a VBA macro code to solve the following problem:

    I have an enquiry table in which column A and B are serial numbers and column C is a question, but can also be a title. My rows start with cell 25 (there is header above).

    In column "B" I wrote (typed) the serial numbers, skipping the cells that have adjacent (in column C) blank ones.

    There is a cell inside the worksheet which allows the user to select (dropdown list) a certain category of queries and thus, there are remaining only the relevant rows.


    I have a VBA code that hide the rows that don't fit with the queries category and also, in the end of the macro code there is the below code that automatically inserts serial numbers in column A, based on hidden rows but ... this is my real issue ... I don't know how to run this code (or, maybe, I need a second one ... ) in order to take into account and skip the blank cells in column B.


    this is the existing and ... incomplete code :

    Code
    1. Set Rng = Range("A25:A" & Range("B" & Rows.Count).End(xlUp).Row)
    2. Dim cntr As Integer: cntr = 1
    3. For Each cell In rRng
    4. If cell.EntireRow.Hidden = False Then
    5. Cells(cell.Row, 1).Value = cntr
    6. cntr = cntr + 1
    7. End If
    8. Next


    e.g. C1=title, C2,C3 =questions, C4=title

    B1="", B2=1, B3=2, B4=""

    If I choose (select) question type C2, then: A1="", A2=1, A3=hidden, A4="" or hidden, and so on


    Thank you so much in advance for your support and I appreciate your kindly effort to spend your time and share your knowledge.