• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Displaying certain number of Columns from right

F

Firasath

New Member
Hi,

I've a database containing multiple columns and rows. I have created an Index with hyperlinks to each sheet. By default, I want to show the last certain number of columns in each sheet when somebody reached to that sheet. Currently it displays all the columns from left. Please find attached a sample of my excel file for your reference.

To be more specific, I want to show last 12 months data by scrolling the columns to the right.
 

Attachments

  • Test.xlsx
    23.3 KB · Views: 5
Dear Narayan,

The solution is not working. It is displaying all columns. it's not scrolling the columns to the right.

To make you understand better, let take Row-9 which is updated till Jul-17. I want make Row-9 as a reference and display data from 12 months before i.e. from Aug-16 (Data should be displayed for period Aug-16 to Jul-17).

Next month, when the data is inputted in Aug-17, then the data should be displayed from Sep-17 to Aug-17. Hope it clarified the requirement for you.
 
Firasath

Firstly, Welcome to the Chandoo.org Forums

Your post has been cross-posted, which means it has been seen on other websites. This is considered poor practice, as it can waste peoples time, which could be spent elsewhere, especially if you get a solution and don't notify us.

I also believe a solution has been posted on another site

I encourage you to please read the site rules at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/
 
Hi,

I got the message and I understand the purpose. I have posted before also on different forums but this is the first time I am getting this message. It seems this is a new development and I consider it very positive approach. I will make sure that I will not cross post in future.

I haven't got the desired solution yet. Request you to assist me by working on both forums. I will surely update both forums once I receive the solution.

Thanks.
 
Hi ,

I do not know what you mean by it is not working. If you can explain why it is not working , I may be able to revise the code.

When either of the sheets is activated , scroll to column XX or some such column away from your data and activate the Index tab. Now when you click on the either of the sheets , you will see that the data is on display.

If this is not what you want , please explain in more detail.

Narayan
 
Dear Narayan,

The requirement is to display only last 12 columns with data. Rest all columns on the left should be scrolled. There can be columns on the right side with no data.

To accomplish the same, I thought of a logic which is inputted below. Please check and advise.

I have a code for this purpose which I will put below in the logic.

Step-1: To find the last updated column on Row-9.
Step-2: To select the 12 columns on the left side.

--- I have a code which is working for the first two steps.

Code: 
Sub Macro1()
   
    ActiveSheet.Range("C12").End(xlToRight).Select
    ActiveCell.Offset(0, -11).Select
   
End Sub

Step-3: Now from the current selected column, which is 12 rows to the left, we need to develop a code to scroll the window to the right till the current selected column.

I hope it clarifies the requirement. Apologies if I could not articulate the requirement properly.
 
Sorry Narayan,

I didn't find any change from the previous file shared. Please check the logic provided by me and advise if it is okay to work on that.
 
Hi ,

I am also sorry , but I cannot do anything further.

When I click on the link labelled Test , the tab displays columns P through AA ; columns AB onwards to the right are blank.

When I click on the link labelled Test (2) , the tab displays columns J through U ; columns V onwards to the right are blank.

I assume this is what you asked for.

If it is not , please upload a screenshot of what you expect to see on the screen when you click on each link. Otherwise , I am afraid I cannot help.

Narayan
 
Dear Narayana,

I am extremely sorry for troubling you. I was in Office and was trying from there and the macro in your file was not working from there. Now, I tried your file from home and it is working fine and exactly as I wanted. I am not sure the reason why it was not working from Office. I will check again tomorrow from Office and try to fix it.

Thanks for taking out your time and spending energy. It is really appreciated.

Just a clarification, I have observed that the last 12 columns are determined by data availability in Row-20. I tried to decode the code but could not figure out how. Appreciate if you can elaborate please.
 
Hi ,

I have chosen row 20 because there was data in that row in both tabs.

We can use any other row , but it must be one which will always have data , so that it can be used to detect the right-most column which has data.

Narayan
 
Thank you. I will try to change the row as I require Row-9 for reference which would be updated every month first. I will try myself and if couldn't succeed, will take your help.
 
Are you allowed to run macros in your working environment? Some companies don't allow it.
 
Yeah. I have other macro enabled files which works fine. It is very strange for me why this macro enable file was not working. I will check tomorrow.
 
Cross Posting is fine, as long as you:
1. Notify each post that you have cross posted
2. Advise / share the answer when it is obtained so that
a. People lean from the answer
b. People don't waste any more time trying to solve it
 
You must log in or register to reply here.
Back
Top